USE [master]
GO
IF OBJECT_ID('dbo.sp_DependencyReport') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_DependencyReport AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_DependencyReport
@IncludeDBs VARCHAR(MAX) = NULL,
@ExcludeDBs VARCHAR(MAX) = 'tempdb,model,msdb,LiteSpeedLocal,ReportServer,ReportServerTempDB,Temporary,udb_ltl',
@PrintSQL BIT = 0,
@IgnoreInvalidObjects BIT = 1
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @IncludeDBs IS NULL
AND @ExcludeDBs = 'tempdb,model,msdb,LiteSpeedLocal,ReportServer,ReportServerTempDB,Temporary,udb_ltl'
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_DependencyReport',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
DECLARE @Include TABLE (DBname VARCHAR(128) NOT NULL
PRIMARY KEY CLUSTERED)
INSERT @Include
(DBName)
SELECT DISTINCT
LTRIM(RTRIM(PARSENAME(Field, 1))) AS DbName
FROM Util.dbo.ParseDelimited(REPLACE(REPLACE(REPLACE(@IncludeDBs, ' ', ','), ' ', ','), '
', ','), ',')
WHERE Field <> ''
DECLARE @IncludeCount INT = @@ROWCOUNT
DECLARE @Exclude TABLE (DBname VARCHAR(128) NOT NULL
PRIMARY KEY CLUSTERED)
INSERT @Exclude
(DBName)
SELECT DISTINCT
LTRIM(RTRIM(PARSENAME(Field, 1))) AS DbName
FROM Util.dbo.ParseDelimited(REPLACE(REPLACE(REPLACE(@ExcludeDBs, ' ', ','), ' ', ','), '
', ','), ',')
WHERE Field <> ''
DECLARE @ExcludeCount INT = @@ROWCOUNT
IF OBJECT_ID('TEMPDB..##Dependencies') IS NOT NULL
DROP TABLE ##Dependencies ;
CREATE TABLE ##Dependencies ([DbName] VARCHAR(6) NOT NULL,
[RingTypeName] VARCHAR(128) NULL,
[RingFQN] VARCHAR(1024) NULL,
[RingSchema] VARCHAR(128) NULL,
[RingName] VARCHAR(128) NULL,
[RedTypeName] VARCHAR(128) NULL,
[RedFQN] VARCHAR(MAX) NULL,
[RedServer] VARCHAR(128) NULL,
[RedDatabase] VARCHAR(128) NULL,
[RedSchema] VARCHAR(128) NULL,
[RedName] VARCHAR(128) NULL,
[RingType] VARCHAR(128) NULL,
[RedType] VARCHAR(128) NULL,
[RingObjectId] INT NULL,
[RedObjectId] INT NULL)
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = 'INSERT ##Dependencies WITH (TABLOCK) ([DbName], [RingTypeName], [RingFQN], [RingSchema], [RingName], [RedTypeName], [RedFQN], [RedServer], [RedDatabase], [RedSchema], [RedName], [RingType], [RedType], [RingObjectId], [RedObjectId])
' + Util.dbo.StringConcat('SELECT ''' + DBname + ''' AS DbName,
otd.Typename AS RingTypeName,
''[' + DBname + '].'' + ISNULL(QUOTENAME(sd.NAME) + ''.'', '''') + QUOTENAME(ISNULL(od.name, tr.name)) AS RingFQN,
sd.name AS RingSchema,
ISNULL(od.name, tr.name) AS RingName,
otg.TypeName AS RedTypeName,
ISNULL(QUOTENAME(ed.referenced_server_name) + ''.'', '''') + ISNULL(QUOTENAME(ISNULL(ed.referenced_database_name, ''' + DBname + ''')) + ''.'', '''')
+ QUOTENAME(COALESCE(ed.referenced_schema_name, mt.RedSchema, xt.RedSchema, ''.'')) + ''.'' + QUOTENAME(ed.referenced_entity_name) AS RedFQN,
ed.referenced_server_name AS RedServer,
COALESCE(ed.referenced_database_name, mt.RedDatabase, xt.RedDatabase, ''' + DBname + ''') AS RedDatabase,
COALESCE(ed.referenced_schema_name, mt.RedSchema, xt.RedSchema) AS RedSchema,
ed.referenced_entity_name AS RedName,
od.type COLLATE SQL_Latin1_General_CP1_CI_AS AS RingType,
COALESCE(og.type, mt.RedType, xt.RedType) COLLATE SQL_Latin1_General_CP1_CI_AS AS RedType,
od.object_id as RingObjectId,
COALESCE(og.object_id, mt.RedObjectId, xt.RedObjectId) AS RedObjectId
FROM ' + DBname + '.sys.sql_expression_dependencies ed (NOLOCK)
LEFT OUTER JOIN ' + DBname + '.sys.objects od (NOLOCK) ON od.object_id = ed.referencing_id
LEFT OUTER JOIN ' + DBname + '.sys.triggers tr (NOLOCK) ON tr.object_id = ed.referencing_id
AND od.object_id IS NULL
LEFT OUTER JOIN ' + DBname + '.sys.schemas sd (NOLOCK) ON sd.schema_id = od.schema_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes otd ON ISNULL(od.type, tr.type) = otd.Type COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN ' + DBname + '.sys.objects og (NOLOCK) ON og.object_id = ed.referenced_id
LEFT OUTER JOIN ' + DBname + '.sys.schemas sg (NOLOCK) ON sg.schema_id = og.schema_id
OUTER APPLY(SELECT ''master'' AS RedDatabase, s.name as RedSchema, o.type AS RedType, o.object_id as RedObjectId
FROM master.sys.procedures o (NOLOCK)
INNER JOIN master.sys.schemas s (NOLOCK) ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped = 1
AND ed.referenced_database_name IS NULL
AND ISNULL(ed.referenced_schema_name, s.name) = s.name
AND ed.referenced_entity_name = o.name
AND LEFT(ed.referenced_entity_name, 3) = ''sp_'')mt
OUTER APPLY(SELECT ''master'' AS RedDatabase, s.name as RedSchema, ''X'' AS RedType, o.id as RedObjectId
FROM master.sys.sysobjects o (NOLOCK)
INNER JOIN master.sys.schemas s (NOLOCK) ON o.uid = s.schema_id
WHERE o.type = ''X''
AND ed.referenced_database_name IS NULL
AND mt.RedObjectId IS NULL
AND ISNULL(ed.referenced_schema_name, s.name) = s.name
AND ed.referenced_entity_name = o.name
AND LEFT(ed.referenced_entity_name, 3) IN(''sp_'', ''xp_'') )xt
LEFT OUTER JOIN Util.dbo.SystemObjectTypes otg ON otg.Type = COALESCE(og.type, mt.RedType, xt.RedType) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE ed.referenced_minor_id = 0
AND ed.referencing_minor_id = 0
AND sd.NAME IS NOT NULL
AND ed.referenced_schema_name IS NOT NULL
AND (ed.referenced_database_name IS NULL OR (ed.referenced_database_name IS NOT NULL AND DB_ID(ed.referenced_database_name) IS NOT NULL))
UNION ALL
SELECT ''' + DBname + ''' AS DbName,
ot.Typename AS RingTypeName,
''[' + DBname + '].'' + QUOTENAME(s.NAME) + ''.'' + QUOTENAME(o.name) AS RingFQN,
s.name AS RingSchema,
o.name AS RingName,
''ASSEMBLY'' AS RedTypeName,
''[' + DBname + '].'' + QUOTENAME(a.NAME) AS RedFQN,
NULL AS RedServer,
''' + DBname + ''' AS RedDatabase,
NULL AS RedSchema,
a.name AS RedName,
o.type COLLATE SQL_Latin1_General_CP1_CI_AS AS RingType,
''ASSEMBLY'' AS RedType,
NULL as RingObjectId,
NULL as RedObjectId
FROM ' + DBname + '.sys.assembly_modules m (NOLOCK)
INNER JOIN ' + DBname + '.sys.assemblies a (NOLOCK) ON a.assembly_id = m.assembly_id
INNER JOIN ' + DBname + '.sys.objects o (NOLOCK) ON o.object_id = m.object_id
INNER JOIN ' + DBname + '.sys.schemas s (NOLOCK) ON s.schema_id = o.schema_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS', '
UNION ALL
')
FROM master.sys.databases d (NOLOCK)
CROSS APPLY (SELECT CAST ( d.name AS VARCHAR (MAX)) AS DBname) db
WHERE ((@IncludeCount > 0
AND EXISTS ( SELECT *
FROM @Include
WHERE dbname = d.name ))
OR @IncludeCount = 0)
AND ((@ExcludeCount > 0
AND NOT EXISTS ( SELECT *
FROM @Exclude
WHERE dbname = d.name ))
OR @ExcludeCount = 0)
IF ISNULL(@SQL, '') = ''
BEGIN
RAISERROR('Cannot build SQL!',16,1)
RETURN
END
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
EXEC(@SQL)
SELECT @SQL = Util.dbo.StringConcat(DISTINCT 'UPDATE a SET RedType = o.type COLLATE SQL_Latin1_General_CP1_CI_AS, RedTypeName = ot.TypeName COLLATE SQL_Latin1_General_CP1_CI_AS, RedObjectId = o.object_Id
FROM ##Dependencies a
INNER JOIN [' + RedDatabase + '].sys.objects o (NOLOCK) ON a.RedName COLLATE SQL_Latin1_General_CP1_CI_AS = o.NAME COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN [' + RedDatabase + '].sys.schemas s(NOLOCK) ON s.schema_id = o.schema_id AND s.NAME COLLATE SQL_Latin1_General_CP1_CI_AS = RedSchema COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = ot.Type COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE a.RedDatabase = ''' + RedDatabase + '''
AND a.RedType IS NULL', '
')
FROM ##Dependencies
WHERE RedType IS NULL
AND DB_ID(RedDatabase) IS NOT NULL
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @SQL <> ''
EXEC(@SQL)
IF OBJECT_ID('tempdb..#all') IS NOT NULL
DROP TABLE #all
SELECT
DISTINCT
*
INTO #all
FROM (SELECT dbName,
RingTypeName AS TypeName,
RingFQN AS FQN,
RingSchema AS [Schema],
RingName AS Name,
RingObjectId AS ObjectId
FROM ##Dependencies
UNION ALL
SELECT RedDatabase AS dbName,
RedTypeName,
RedFQN,
RedSchema,
RedName,
RedObjectId AS ObjectId
FROM ##Dependencies) k
IF OBJECT_ID('tempdb..#lnk') IS NOT NULL
DROP TABLE #lnk
SELECT DISTINCT
RingFQN AS OrigFQN,
RingFQN,
RedFQN,
CAST(RedFQN AS VARCHAR(MAX)) AS RedHierarchy,
1 AS DEPTH
INTO #lnk
FROM ##Dependencies
WHERE RingFQN <> RedFQN
WHILE 1 = 1
BEGIN
INSERT #lnk WITH (TABLOCK)
([OrigFQN],
[RingFQN],
[RedFQN],
RedHierarchy,
DEPTH)
SELECT l.OrigFQN,
d.RingFQN,
d.RedFQN,
RedHierarchy + ', ' + d.RedFQN AS RedHierarchy,
l.DEPTH + 1
FROM #lnk l
INNER JOIN ##Dependencies d ON l.RedFQN = d.RingFQN
WHERE NOT EXISTS ( SELECT *
FROM #lnk l2
WHERE l2.OrigFQN = l.OrigFQN
AND l2.RedFQN = d.RedFQN )
AND l.OrigFQN <> d.RedFQN
IF @@ROWCOUNT = 0
BREAK
END
IF OBJECT_ID('tempdb..##DependencyResult') IS NOT NULL
DROP TABLE ##DependencyResult
SELECT l.DEPTH,
COUNT(*) OVER (PARTITION BY l.OrigFQN) AS ReferenceCnt,
ROW_NUMBER() OVER (PARTITION BY l.OrigFQN ORDER BY l.DEPTH, l.RingFQN , l.RedFQN) AS ReferenceRow,
l.OrigFQN AS RootFQN,
s.TypeName AS RootType,
s.dbName AS RootDB,
s.[Schema] AS RootSchema,
s.Name AS RootName,
l.RingFQN AS ReferencingFQN,
m.TypeName AS ReferencingType,
m.dbName AS ReferencingDB,
m.[Schema] AS ReferencingSchema,
m.Name AS ReferencingName,
l.RedFQN AS ReferencedFQN,
r.TypeName AS ReferencedType,
r.dbName AS ReferencedDB,
r.[Schema] AS ReferencedSchema,
r.Name AS ReferencedName,
RedHierarchy,
s.ObjectId AS RootObjectId,
m.ObjectId AS ReferencingObjectId,
r.ObjectId AS ReferencedObjectId
INTO ##DependencyResult
FROM #lnk l
LEFT OUTER JOIN #all s ON s.FQN = l.OrigFQN
LEFT OUTER JOIN #all m ON m.FQN = l.RingFQN
LEFT OUTER JOIN #all r ON r.FQN = l.RedFQN
WHERE (@IgnoreInvalidObjects = 0
OR (r.ObjectId IS NOT NULL
OR ISNULL(r.TypeName, '') IN ('ASSEMBLY')))
ORDER BY RootFQN,
DEPTH,
ReferencingFQN,
ReferencedFQN
PRINT 'SELECT Depth, ReferenceCnt, ReferenceRow, RootFQN, RootType, RootDB, RootSchema, RootName, ReferencingFQN, ReferencingType, ReferencingDB, ReferencingSchema, ReferencingName, ReferencedFQN, ReferencedType, ReferencedDB, ReferencedSchema, ReferencedName, RedHierarchy, RootObjectId, ReferencingObjectId, ReferencedObjectId
FROM ##DependencyResult'
SELECT *
FROM ##DependencyResult
GO
EXEC sys.sp_ms_marksystemobject
sp_DependencyReport
GO