【SQL Server点滴积累】SQL Server 2008 查看依赖关系

本文链接：https://blog.youkuaiyun.com/ncutyb123/article/details/133615011

本文提供两种SQL Server 2008中查询对象依赖关系的方法。一种直接查询sys.sql_expression_dependencies视图来找出视图v_searchall的依赖实体；另一种采用递归公用表表达式的方式逐层深入查找所有依赖项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

广告位招租！
知识无价，人有情，无偿分享知识，希望本条信息对你有用！

SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name 
    ,referenced_server_name AS server_name
    ,referenced_database_name AS database_name
    ,referenced_schema_name AS schema_name
    , referenced_entity_name
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID(N'dbo.v_searchall');

分层查看依赖关系

DECLARE @referencing_entity AS sysname;
SET @referencing_entity = N'v_searchall';

WITH ObjectDepends(entity_name,referenced_schema, referenced_entity, referenced_id,level)
AS (
    SELECT entity_name = 
       CASE referencing_class
          WHEN 1 THEN OBJECT_NAME(referencing_id)
          WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t 
                       WHERE t.object_id = sed.referencing_id)
          WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
                       WHERE st.object_id = sed.referencing_id) COLLATE database_default
       END
    ,referenced_schema_name
    ,referenced_entity_name
    ,referenced_id
    ,0 AS level 
    FROM sys.sql_expression_dependencies AS sed 
    WHERE OBJECT_NAME(referencing_id) = @referencing_entity 
UNION ALL
    SELECT entity_name = 
       CASE sed.referencing_class
          WHEN 1 THEN OBJECT_NAME(sed.referencing_id)
          WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t 
                       WHERE t.object_id = sed.referencing_id)
          WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
                       WHERE st.object_id = sed.referencing_id) COLLATE database_default
       END
    ,sed.referenced_schema_name
    ,sed.referenced_entity_name
    ,sed.referenced_id
    ,level + 1   
    FROM ObjectDepends AS o
    JOIN sys.sql_expression_dependencies AS sed ON sed.referencing_id = o.referenced_id
    )
SELECT entity_name,referenced_schema, referenced_entity, level
FROM ObjectDepends
ORDER BY level;