SQL SERVER 2008 开发系列（十四）

T-SQL依赖项

/**********************************************************
Author="WZ"
Create Date="2008/9/7"
SQL SERVER 2008 开发系列（十四) T-SQL依赖项
**********************************************************/

USE AdventureWorks
GO

--sys.sql_dependencies 元数据视图
--数据库对象依赖关系
SELECT DISTINCT OBJECT_NAME(object_id) AS '引用对象',
    OBJECT_SCHEMA_NAME(referenced_major_id) + '.' +
    OBJECT_NAME(referenced_major_id) AS '被引用对象'
FROM  sys.sql_dependencies
WHERE object_id = object_id('HumanResources.vEmployee')
GO

--sys.sql_expression_dependecies 元数据视图
SELECT OBJECT_NAME(referencing_id) AS '引用对象',
    referenced_schema_name + '.' + referenced_entity_name AS '被引用对象'
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'HumanResources.vEmployee');

GO

--sys.dm_sql_referenced_entities 引用实体管理函数
--查看HumanResources.vEmployee视图依赖的数据列
SELECT referenced_schema_name + '.' + referenced_entity_name
       + '.' + referenced_minor_name AS '视图引用列'
    FROM sys.dm_sql_referenced_entities(N'HumanResources.vEmployee', 'OBJECT')
    WHERE referenced_minor_id <> 0
GO

--查看Person.Contact视图依赖的数据列
SELECT referencing_schema_name + '.' + referencing_entity_name AS '表相关对象'
    FROM sys.dm_sql_referencing_entities('Person.Contact', 'OBJECT')
GO