[SQL]查看物件的相依性SQL Object Dependency

2013-07-26

4026
0
SQL

SQL 2008之後有提供sys.dm_sql_referencing_entities、sys.dm_sql_referenced_entities及sys.sql_expression_dependencies 動態管理檢視和函數，可讓我們取得SQL物件的相依性。

SQL 2008之後有提供sys.dm_sql_referencing_entities、sys.dm_sql_referenced_entities及sys.sql_expression_dependencies 動態管理檢視和函數。

可透過它們來取得SQL物件的相依性!

如下，我查看dbo.fnGET_DEPART_M1這個函數被使用及使用到的相關物件，


--查看有用到dbo.fnGET_DEPART_M1的相關物件 
SELECT *
FROM sys.dm_sql_referencing_entities ('dbo.fnGET_DEPART_M1', 'OBJECT');

SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name, 
    o.type_desc AS referencing_desciption, 
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, 
    referencing_class_desc, referenced_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name, 
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
    is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referenced_id = OBJECT_ID(N'dbo.fnGET_DEPART_M1');


 
--查看有被dbo.fnGET_DEPART_M1使用到的物件
SELECT *
FROM sys.dm_sql_referenced_entities ('dbo.fnGET_DEPART_M1', 'OBJECT');

SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, 
    o.type_desc AS referencing_desciption, 
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, 
    referencing_class_desc, referenced_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name, 
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
    is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(N'dbo.fnGET_DEPART_M1');