April 10, 2014 at 9:18 am
I need to produce a list of UDFs that are used in replicated views (Transactional).
The Views to underlying UDFs is all good. (INFORMATION_SCHEMA.VIEW_TABLE_USAGE)
But the names of the Views in a publication elude me.
I created a test transactional publication with 1 view, 1 SP and 1 udf. Then created a subscription.
SELECT* FROMsysPublications
SELECT* FROMsysSubscriptions
SELECT* FROMsysArticles
(See the attachment for the results)
sysSubscriptions has 3 articles that are not in the sysArticles table.
The closest I've come to finding an answer is here.
...such changes are delivered by taking a snapshot of their content on the publisher and then applying that snapshot to the subscriber(s).
Fascinating, but doesn't help my cause.
So, who knows where one can find the names of replicated SP, UDFs and VIEWs using TSQL?
Thanks
April 10, 2014 at 11:41 am
Tables, views, procedures, and functions are stored in the DistributionDB.dbo.MSarticles table:SELECT * FROM distribution.dbo.MSarticles WHERE publisher_db = [YourDB]
Hope this helps!
Also, on the publisher you can run the query below to get a proper listing for all published objects:
SELECT name, object_id, type_desc FROM sys.objects
WHERE is_schema_published = 1 AND
type IN ('P', 'V', 'FN')
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 11, 2014 at 7:03 am
Great! Exactly what I needed. Thank you.
Here's my code. Still fresh, so needs to be tested more.
-- Run this code from the publication database
-- Code assumes some form of naming convention.
-----------------------------------------------------------------------------------------------------------
-- Declare Variables
-----------------------------------------------------------------------------------------------------------
DECLARE@PubName Varchar(128)
SELECT@Pubname = NULL -- Wildcards allowed
-----------------------------------------------------------------------------------------------------------
-- Preperation
-----------------------------------------------------------------------------------------------------------
IF OBJECT_ID('TempDB..#Func') IS NOT NULL
DROP TABLE #Func
-----------------------------------------------------------------------------------------------------------
-- Get all the UDFs used in published view(s) + functions from calculated columns
-----------------------------------------------------------------------------------------------------------
;WITH cteNonTbls
AS
(
SELECTNonTbls.PubName,
NonTbls.ReplArtObj,
O.[Type],
O.Type_Desc
FROM(
-- List of non table articles in the publication
SELECTDistPub.Publication PubName,
DistArt.Source_Object ReplArtObj
FROMdistribution.[dbo].[MSpublications] DistPub
INNER JOIN sysPublications P
ON DistPub.publication = P.Name
INNER JOIN distribution.dbo.MSarticles DistArt
On DistPub.publication_id = DistArt.publication_id
WHERE(@PubName IS NULL
OR DistPub.Publication LIKE @Pubname)
EXCEPT
-- Remove all the tables from the above results (Ignore SPs)
SELECTP.Name PubName,
OBJECT_NAME(A.[objid]) ObjName
FROMdbo.sysPublications P
INNER JOIN dbo.sysArticles A
ON P.PubID = A.PubID
INNER JOIN sys.Objects O
ON A.[ObjID] = O.[Object_ID]
WHERE(@PubName IS NULL
OR P.Name LIKE @PubName)
AND O.[Type] = 'U'
) NonTbls
INNER JOIN sys.Objects O
ON NonTbls.ReplArtObj = O.Name
)
SELECTDISTINCT FuncList.FuncName,
FuncList.FuncType,
FuncList.FuncTypeDesc
INTO#Func
FROM(
SELECTVTU1.VIEW_NAME ViewName,
VTU1.TABLE_NAME FuncName,
O.[Type] FuncType,
O.[type_desc] FuncTypeDesc
FROMINFORMATION_SCHEMA.VIEW_TABLE_USAGE VTU1
INNER JOIN sys.Objects O
ON VTU1.TABLE_NAME = O.Name
INNER JOIN cteNonTbls
ON VTU1.VIEW_NAME = cteNonTbls.ReplArtObj
AND O.Type_Desc LIKE '%FUNCTION%'
UNION
SELECT*
FROMcteNonTbls
) FuncList
WHEREFuncTypeDesc LIKE '%FUNCTION%'
UNION
-- Add in any functions from calculated columns that are in the publication(s)
SELECTDISTINCT
UDFs.FuncName,
UDFs.FuncType,
UDFs.FuncTypeDesc
FROMsys.computed_columns CC
INNER JOIN sys.Objects O
ON O.[object_id] = CC.[object_id]
INNER JOIN sysArticles A
ON A.[objid] = O.[object_id]
INNER JOIN sysPublications P
ON P.Pubid = A.Pubid
CROSS APPLY
(
SELECTO2.Name FuncName,
O2.[type] FuncType,
O2.type_desc FuncTypeDesc
FROMsys.Objects O2
WHERECC.[definition] LIKE '%' + O2.Name + '%'
AND O2.type_desc LIKE '%Function%'
) UDFs
WHERE(@PubName IS NULL
OR P.Name LIKE @PubName)
SELECT*
FROM#Func
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply