SQLServer 查看对象间依赖关系

在SQL Server中，(可编程)对象间的引用即依赖关系，有多种方式可以检查，随着版本变更，方式也有所不同。

父子关系的对象，不通过依赖关系来查询，比如：

1. 外键关系

use tempdbGO--drop table tb1,tb2create table tb1(col1 int Primary key,col2 int)insert into tb1 values (2,2),(3,2),(4,2),(5,2)GOcreate table tb2(col3 int primary key,col4 int constraint FK_tb2 foreign key  references tb1(col1))GO--检查外键select object_name(constraint_object_id) constraint_name,       object_name(parent_object_id) parent_object_name,       col_name(parent_object_id,parent_column_id) parent_object_column_name,       object_name(referenced_object_id) referenced_object_name,       col_name(referenced_object_id,referenced_column_id) referenced_object_column_name from sys.foreign_key_columnswhere referenced_object_id = object_id('tb1')

2. 表上的索引，触发器

use tempdbGOif OBJECT_ID('T','U') is not null    drop table Tcreate table T(id int)GOif exists(select 1 from sys.indexes where name = 'IX_001' and object_id = object_id('T','U'))    drop index T.IX_001create index IX_001 on T(id)if OBJECT_ID ('test_dml_trigger', 'TR') is not null   drop trigger test_dml_triggerGOcreate trigger test_dml_triggerON TAFTER INSERT, UPDATE AS RAISERROR ('Notify Customer Relations', 16, 10);GO--检查索引select object_name(object_id) as table_name,* from sys.indexes where name = 'IX_001' and object_id = object_id('T','U')--检查DML触发器select name as table_name, object_name(a.parent_obj) as dml_trigger_namefrom sysobjects awhere a.xtype = 'TR'

 

在SSMS中，数据库对象上右击/View Dependencies，可以查看到对象的依赖关系，那么用脚本怎么检查？

create database DB1;create database DB2;use DB1GOif OBJECT_ID('T1','U') is not null    drop table T1GOcreate table T1(id int);GOif OBJECT_ID('V1','V') is not null    drop view V1GOcreate view V1as select * from T1GOif OBJECT_ID('SP1','P') is not null    drop proc SP1GOcreate proc SP1asselect * from V1GOuse DB2GOif OBJECT_ID('SP2','P') is not null    drop proc SP2GOcreate proc SP2asselect * from DB1..V1GOuse DB1GOif OBJECT_ID('SP3','P') is not null    drop proc SP3GOcreate proc SP3asexec DB2..SP2GOuse DB1GOif object_id('test_schema.T2','U') is not null    drop table test_schema.T2GOif exists(select 1 from sys.schemas where name  = 'test_schema')    drop schema test_schemaGOcreate schema test_schemacreate table test_schema.T2(c1 int, c2 int)GOif OBJECT_ID('SP4','P') is not null    drop proc SP4GOcreate proc SP4asselect * from test_schema.T2GO

 

一. SQL Server 2000依赖关系查询

--从SQL Server 2000沿用下来的系统表，SQL Server 2016仍适用，后续版本将不再支持USE DB1SELECT o.name, o.xtype, p.name as referenced_name, p.xtypeFROM sysdepends dINNER JOIN sysobjects o    ON d.id = o.idINNER JOIN sysobjects p    ON d.depid = p.id--从SQL Server 2000沿用下来的存储过程，SQL Server 2016仍适用，后续版本将不再支持USE DB1exec sp_depends 'V1'--无文档记载的sp_MS存储过程，只能检查被自己引用的对象，SQL Server 2016仍适用exec sp_MSdependencies 'V1'

注意：sysdepends, sp_depends, sp_MSdependencies 只能检查当前数据库对象的引用/被引用，对于跨数据库对象依赖关系，无法检查。

 

二. SQL Server 2005依赖关系查询

--从SQL Server 2005沿用下来的系统视图，SQL Server 2016仍适用，后续版本将不再支持USE DB1SELECT o.name, o.type_desc, p.name as referenced_name, p.type_descFROM sys.sql_dependencies dINNER JOIN sys.objects o    ON d.object_id = o.object_idINNER JOIN sys.objects p    ON d.referenced_major_id = p.object_id

注意：和sysdepends, sp_depends一样，sys.sql_dependencies只能检查当前数据库对象的引用/被引用，对于跨数据库对象依赖关系，无法检查。

 

三. SQL Server 2008后依赖关系查询

--从SQL Server 2008开始用的系统视图USE DB1SELECT o.name, o.type_desc, p.name as referenced_name, p.type_descFROM sys.sql_expression_dependencies dINNER JOIN sys.objects o    ON d.referencing_id = o.object_idINNER JOIN sys.objects p    ON d.referenced_id = p.object_idUSE DB1--从SQL Server 2008开始用的系统函数，引用我的对象SELECT * FROM sys.dm_sql_referencing_entities('dbo.V1','OBJECT')--从SQL Server 2008开始用的系统函数，被我引用的对象SELECT * FROM sys.dm_sql_referenced_entities('dbo.SP1','OBJECT')USE DB2--从SQL Server 2008开始用的系统函数，引用我的对象SELECT * FROM sys.dm_sql_referencing_entities('dbo.SP2','OBJECT')--从SQL Server 2008开始用的系统函数，被我引用的对象SELECT * FROM sys.dm_sql_referenced_entities('dbo.SP2','OBJECT')

注意：

(1) sys.sql_expression_dependencies及这两个新增函数，都可以检查当前数据库中跨数据库，跨服务器引用的对象，但当前数据库对象被跨数据库，跨服务器引用，无法检查；

(2) 新增的2个系统函数，可以更方便的检查引用和被引用，但对象名要完整，必须包含schema name，否则无法返回正确结果；

(3) sys.dm_sql_referenced_entities 还可以查看被数据库/服务器DDL触发器引用的对象；

SELECT * FROM sys.dm_sql_referenced_entities ('ddl_database_trigger_name', 'DATABASE_DDL_TRIGGER');

(4) sys.dm_sql_referencing_entities 还可以查看引用了类型/分区函数等的对象。

 

四. 无法查明的依赖关系

1. 跨数据库/服务器对象

上面提到从SQL Server 2008开始，跨数据库，跨服务器引用的对象，已经可以查询；

但是写法上要稍微调整下，因为当前数据库中，并没有其他数据库对象的object_id，所以不能按照object_id来关联。改动后脚本如下：

USE DB1SELECT schema_name(o.schema_id) as schema_name, o.name as object_name, o.type_desc,        d.referenced_server_name, d.referenced_database_name, isnull(d.referenced_schema_name,'dbo') as referenced_schema_name, d.referenced_entity_nameFROM sys.sql_expression_dependencies dINNER JOIN sys.objects o    ON d.referencing_id = o.object_id

注意：跨数据库/跨服务器对象的引用，仅能检查3部分/4部分名称格式的对象引用，即如：server_name.db_name.schema_name.object_name格式，对于OPENROWSET, OPENQUERY, OPENDATASOURCE的引用并不记录。

 

2. 临时对象

对于存储过程中用到的临时表，只能检查到create table创建的非#开头临时表，并且用函数检查还会报错，因为表事先并不存在。

if OBJECT_ID('SP5','P') is not null    drop proc SP5GOcreate proc SP5asselect * into #temp from sys.objectsselect * into _temp from sys.objectsselect getdate()create table #t (id int)insert into #t select 100if OBJECT_ID('_t','U') is not null    drop proc _tcreate table _t (id int)insert into _t select 100GO

USE DB1--只能检查到create table创建的非#临时表SELECT schema_name(o.schema_id) as schema_name, o.name as object_name, o.type_desc,        d.referenced_server_name, d.referenced_database_name, isnull(d.referenced_schema_name,'dbo') as referenced_schema_name, d.referenced_entity_nameFROM sys.sql_expression_dependencies dINNER JOIN sys.objects o    ON d.referencing_id = o.object_id--并且用函数检查还会报错，因为表事先并不存在select * from sys.dm_sql_referenced_entities('dbo.SP5','OBJECT');/*Msg 2020, Level 16, State 1, Line 4The dependencies reported for entity "dbo.SP5" might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity.  Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.*/

 

3. 动态SQL里引用的对象

use DB1GOif OBJECT_ID('T2','U') is not null    drop table T2GOcreate table T2(id int);GOif OBJECT_ID('SP6','P') is not null    drop proc SP6GOcreate proc SP6asexec('select * from T1')declare @SQL nvarchar(max)set @SQL = N'select * from T2'exec sp_executesql @SQLexec (@SQL)GO

USE DB1--无论系统视图/函数，都查不到SELECT schema_name(o.schema_id) as schema_name, o.name as object_name, o.type_desc,        d.referenced_server_name, d.referenced_database_name, isnull(d.referenced_schema_name,'dbo') as referenced_schema_name, d.referenced_entity_nameFROM sys.sql_expression_dependencies dINNER JOIN sys.objects o    ON d.referencing_id = o.object_id--无论系统视图/函数，都查不到select * from sys.dm_sql_referenced_entities('dbo.SP6','OBJECT');

动态SQL里引用的对象，无论系统视图/函数，都查不到；也许只能试试查可编程对象的文本定义：

--ANSI SQL标准里定义的INFORMATION_SCHEMA对象select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like '%T2%'--SQL Server 2000沿用下来的可编程对象文本定义select * from syscommentswhere text like '%T2%'--SQL Server 2005开始的可编程对象文本定义select * from sys.sql_modules where definition like '%T2%'

 

注意：这种方法，对于hard coding的对象名，非常好用，但是，

(1) 有时动态SQL里的对象名称并不是hard coding，所以也不一定能找到；比如：

EXEC('SELECT * FROM dbo.table' + '_name') EXEC('SELECT * FROM ' + @table)

(2) 另外一些书写不严格的SQL，也无法定位到对象名，比如：

SELECT * FROM dbo . table_name --这语法竟然也能通过SELECT * FROM dbo.table_name_2 --名字只是部分类似，table_name_2不是table_name

 

4. 延迟名称解析

如果被引用的数据库对象，在后面创建，那么用2000或者2005的方式去检查，会出现延迟名称解析(deferred name resolution)，用2008后的方式，已经没有这个问题。

use DB2GOif OBJECT_ID('T3','U') is not null    drop table T3GOcreate table T3(id int);GO--引用的SP_1st后创建if OBJECT_ID('SP_2nd','P') is not null    drop proc SP_2ndGOcreate proc SP_2ndasexec SP_1stGOif OBJECT_ID('SP_1st','P') is not null    drop proc SP_1stGOcreate proc SP_1stasselect * from T3GO

--出现延迟名称解析(deferred name resolution): 存储过程SP_2nd的引用对象，无法获取到USE DB2SELECT o.name, o.xtype, p.name as referenced_name, p.xtypeFROM sysdepends dINNER JOIN sysobjects o    ON d.id = o.idINNER JOIN sysobjects p    ON d.depid = p.idexec sp_depends 'SP_1st'exec sp_depends 'SP_2nd'USE DB2SELECT o.name, o.type_desc, p.name as referenced_name, p.type_descFROM sys.sql_dependencies dINNER JOIN sys.objects o    ON d.object_id = o.object_idINNER JOIN sys.objects p    ON d.referenced_major_id = p.object_id--刷新对象定义，可以解决exec sp_refreshsqlmodule 'SP_2nd'--如果是视图，也可以这样刷新exec sp_refreshview 'view_name'--使用2008后的系统视图，没有这个问题，它同时保存了引用对象的名称，object_id可先置为NULLUSE DB2SELECT schema_name(o.schema_id) as schema_name, o.name as object_name, o.type_desc,        d.referenced_server_name, d.referenced_database_name, isnull(d.referenced_schema_name,'dbo') as referenced_schema_name, d.referenced_entity_nameFROM sys.sql_expression_dependencies dINNER JOIN sys.objects o    ON d.referencing_id = o.object_id

注意：新的视图虽然解决了延迟名称解析的问题，但也带来了新问题，如果引用的对象一直未被创建，或者创建后被重名命/删除，这条依赖关系仍然存在。

 

五. 如何获取多层嵌套引用的对象

有时一个对象下会多层嵌套引用数据库对象，尤其是视图/存储过程等的嵌套调用，在某些场景下获取所有嵌套调用的对象很有用，比如：要更新某个存储过程下所有引用到的表上的统计信息。

use DB2GOcreate table dbo.table2(c2 int)GOcreate proc dbo.sp12asselect * from table2GOuse DB1GOcreate table dbo.table1(c1 int)GOcreate view dbo.view1 as select * from dbo.table1GOcreate view dbo.view2 as select * from dbo.view1GOcreate proc dbo.sp11asselect * from dbo.view2GOcreate proc dbo.sp13as exec dbo.sp11exec DB2.dbo.sp12GO

use DB2GOdeclare @entity_name varchar(512)set @entity_name = 'dbo.sp13';with tmpas(SELECT *FROM sys.sql_expression_dependencies dWHERE d.referencing_id = object_id(@entity_name)union allSELECT d.*FROM sys.sql_expression_dependencies dINNER JOIN tmp t   ON t.referenced_id = d.referencing_id)--select * from tmpSELECT schema_name(o.schema_id) as schema_name, o.name as object_name, o.type_desc,        d.referenced_server_name, d.referenced_database_name, isnull(d.referenced_schema_name,'dbo') as referenced_schema_name, d.referenced_entity_name  FROM tmp d INNER JOIN sys.objects o    ON d.referencing_id = o.object_id -- LEFT JOIN sys.objects ro --   ON d.referenced_id = ro.object_id --WHERE ro.type_desc = 'USER_TABLE' or ro.type_desc is null

注意:

(1) 最后注释的几行脚本，限制用来获取所有被引用到的表，可根据需要调整；

(2) 跨数据库/服务器引用的对象，如果不是最后一层，还得切换到对应的数据库/服务器再运行此脚本。

 

小结：

1. 查看被哪些对象引用，sys.sql_expression_dependencies，sys.dm_sql_referencing_entities， sys.sql_modules，无论哪种方式都查不到被跨数据库引用；

2. 查看引用了哪些对象，sys.sql_expression_dependencies，sys.dm_sql_referenced_entities，都可以查到跨数据库引用的对象，如果查看嵌套调用的对象，还是递归查询sys.sql_expression_dependencies比较直接。

源文：http://www.cnblogs.com/seusoftware/p/4858115.html