Replacing sp_depends with sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities
0/5 (0 vote)
How to replace sp_depends with sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities
sp_depends have been one of the most used system stored procedures in SQL Server. In fact, many of us still use that even though Microsoft had annouced that it will be removed from the future releases.
Alternatively, Microsoft has provided two dynamic management views (these have been introduced with SQL Server 2008) in order to get similar kind of information.
You can get further details on the aforementioned view by visiting the link. (Links are embedded into the view name.)
However, if you have used sp_depends, you might have already faced the issue that the results which are being returned from this stored procedure are not very accurate (most of the time, it seems fine).
The other day, I was going through these two views in order to create an sp which is similar to sp_depends and thought of sharing the query so that it can be useful to anyone who depends on this sp.
DECLARE
@objname AS NVARCHAR(100) = 'Website.SearchForPeople'
,@objclass AS NVARCHAR (60) = 'OBJECT'
SELECT
CONCAT(sch.[name],'.',Obj.[name]) AS [name]
,(CASE Obj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'PK' THEN 'PRIMARY KEY constraint'
WHEN 'R' THEN 'Rule (old-style, stand-alone)'
WHEN 'TA' THEN 'Assembly (CLR-integration) trigger'
WHEN 'TR' THEN 'SQL trigger'
WHEN 'UQ' THEN 'UNIQUE constraint'
WHEN 'AF' THEN 'Aggregate function (CLR)'
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'SQL scalar function'
WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
WHEN 'IF' THEN 'SQL inline table-valued function'
WHEN 'IT' THEN 'Internal table'
WHEN 'P' THEN 'SQL Stored Procedure'
WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
WHEN 'PG' THEN 'Plan guide'
WHEN 'PK' THEN 'PRIMARY KEY constraint'
WHEN 'R' THEN 'Rule (old-style, stand-alone)'
WHEN 'RF' THEN 'Replication-filter-procedure'
WHEN 'S' THEN 'System base TABLE'
WHEN 'SN' THEN 'Synonym'
WHEN 'SO' THEN 'Sequence OBJECT'
WHEN 'U' THEN 'Table (user-defined)'
WHEN 'V' THEN 'VIEW'
WHEN 'SQ' THEN 'Service queue'
WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
WHEN 'TF' THEN 'SQL table-valued-function'
WHEN 'TR' THEN 'SQL DML trigger'
WHEN 'TT' THEN 'Table type'
WHEN 'UQ' THEN 'UNIQUE CONSTRAINT'
WHEN 'X' THEN 'Extended stored procedure'
ELSE 'Undefined'
END) AS [type]
,Obj.create_date
,Obj.modify_date
,src.referenced_minor_name AS [column]
,IIF(src.is_selected = 1,'yes','no') AS is_selected
,IIF(src.is_updated = 1,'yes','no') AS is_updated
,IIF(src.is_select_all = 1,'yes','no') AS is_select_all
,IIF(src.is_insert_all = 1,'yes','no') AS is_insert_all
FROM
sys.dm_sql_referenced_entities (@objname,@objclass) AS src
JOIN sys.objects AS Obj
ON src.referenced_id = Obj.[object_id]
JOIN sys.schemas AS Sch
ON Sch.[schema_id] = Obj.[schema_id]
WHERE 1=1
SELECT
CONCAT(Src.referencing_schema_name,'.',Src.referencing_entity_name) AS [name]
,(CASE Obj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'PK' THEN 'PRIMARY KEY constraint'
WHEN 'R' THEN 'Rule (old-style, stand-alone)'
WHEN 'TA' THEN 'Assembly (CLR-integration) trigger'
WHEN 'TR' THEN 'SQL trigger'
WHEN 'UQ' THEN 'UNIQUE constraint'
WHEN 'AF' THEN 'Aggregate function (CLR)'
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'SQL scalar function'
WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
WHEN 'IF' THEN 'SQL inline table-valued function'
WHEN 'IT' THEN 'Internal table'
WHEN 'P' THEN 'SQL Stored Procedure'
WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
WHEN 'PG' THEN 'Plan guide'
WHEN 'PK' THEN 'PRIMARY KEY constraint'
WHEN 'R' THEN 'Rule (old-style, stand-alone)'
WHEN 'RF' THEN 'Replication-filter-procedure'
WHEN 'S' THEN 'System base TABLE'
WHEN 'SN' THEN 'Synonym'
WHEN 'SO' THEN 'Sequence OBJECT'
WHEN 'U' THEN 'Table (user-defined)'
WHEN 'V' THEN 'VIEW'
WHEN 'SQ' THEN 'Service queue'
WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
WHEN 'TF' THEN 'SQL table-valued-function'
WHEN 'TR' THEN 'SQL DML trigger'
WHEN 'TT' THEN 'Table type'
WHEN 'UQ' THEN 'UNIQUE CONSTRAINT'
WHEN 'X' THEN 'Extended stored procedure'
ELSE 'Undefined'
END) AS [type]
,Obj.create_date
,Obj.modify_date
FROM
sys.dm_sql_referencing_entities (@objname,@objclass) AS Src
JOIN sys.objects AS Obj
ON Obj.[object_id] = Src.referencing_id
I have even compiled a stored procedure using this syntax and it can be found in the following repository: