用 sys.dm_sql_referencing_entities 和 sys.dm_sql_referenced_entities 替换 sp_depends
0/5 (0投票)
如何用 sys.dm_sql_referencing_entities 和 sys.dm_sql_referenced_entities 替换 sp_depends
sp_depends 一直是 SQL Server 中最常用的系统存储过程之一。事实上，我们许多人仍然在使用它，即使微软已经宣布它将在未来的版本中移除。
作为替代方案，微软提供了两个动态管理视图（这些视图是在 SQL Server 2008 中引入的），以便获取类似的信息。
您可以访问链接以获取有关上述视图的更多详细信息。（链接嵌入在视图名称中。）
然而，如果您使用过 sp_depends，您可能已经遇到过该存储过程返回的结果不太准确的问题（大多数情况下，看起来都还好）。
前几天，我正在研究这两个视图，以便创建一个类似于 sp_depends 的 sp，并想分享这个查询，以便它可以对依赖于这个 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
我已经使用这种语法编译了一个存储过程，可以在以下存储库中找到