How to find SQL Object dependencies – George Palacios

From SQL Server 2008 and onward we gained a new DMV called sys.dm_sql_referenced_entities – this allows us to track object dependencies rather neatly.

This covers all dependencies including cross-database and cross-server – it does not however make it easier to find the type of object being referenced (What does OBJECT_OR_COLUMN even mean?).

I’ve written the below to cover this eventuality – it will find all cross-database references and also the object type.

DECLARE @ObjectName SYSNAME = 'dbo.usp_Name';
 
IF (OBJECT_ID(@ObjectName) IS NULL)
BEGIN;
	throw 51000,'Object does not exist',1;
END
 
DECLARE @SQL NVARCHAR(MAX) = N'';
 
CREATE TABLE #TempDBNames (
	id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	DBName sysname NOT NULL
	);
 
INSERT INTO [#TempDBNames] ([DBName])
SELECT DISTINCT (Referenced_Database_name)
FROM sys.dm_sql_referenced_entities(@ObjectName,'OBJECT') d
WHERE [d].[referenced_database_name] IS NOT NULL
 
SET @SQL = 
'SELECT ISNULL(d.referenced_database_name,DB_NAME()) AS DependentOnDatabase,
	ISNULL(d.referenced_schema_name,''dbo'') AS DependentOnSchema,
	d.referenced_entity_name AS DependentOnObject,
	d.referenced_minor_name AS DependentOnMinorObject,
	CASE
	WHEN d.referenced_database_name IS NULL THEN o.type_desc
	'
SELECT @SQL += 
	'WHEN d.referenced_database_name = ''' + [DBName] + ''' THEN '
 
+ QUOTENAME(CAST([id] AS NVARCHAR(MAX)))+ '.type_desc
	'
FROM [#TempDBNames]
 
SELECT @SQL += '
END AS ObjectType
FROM sys.dm_sql_referenced_entities(''' + @ObjectName + ''', ''OBJECT'') d
	LEFT JOIN sys.objects o ON d.referenced_id = o.object_id
'
 
 
SELECT @SQL += 
'    LEFT JOIN ' + QUOTENAME([DBName]) + '.sys.objects' + char(32) +
 
QUOTENAME(CAST([id] AS NVARCHAR(MAX))) + ' ON d.referenced_id = ' +
 
QUOTENAME(CAST([id] AS NVARCHAR(MAX))) + '.object_id'
FROM [#TempDBNames]
 
--PRINT(@SQL);
EXEC(@SQL);
 
DROP TABLE [#TempDBNames]

Leave a Reply Cancel reply