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