使用sql命令查询视图中所有引用的基础表

创建时间：2013年06月27日 10:22 阅读次数：(14741)

分享到：

之前有写过如何利用sql查询视图中所有引用的表发现这个方法并不能查出视图中所有的基础表，如果视图中有嵌套视图就会有问题，因为目录视图sys.sql_dependencies并不包含所有的引用实体。后面发现在sql2008及以后的版本中推出的sys.sql_expression_dependencies视图解决了这一问题，所以重新写了段sql，用来查询视图中所有引用的基础表，包括嵌套视图中的基础表。这个有什么作用呢，各人有各人的用处吧，我是用来估计和优化视图的效率的，如果视图中引用的基础表太多，是不是就会在一定程度上影响性能呢？

查询的sql语句如下：

declare @v_name varchar(50)
set @v_name='v_view' --需要查询的视图名

declare @t_temp_referenced table
(
tname varchar(50),
ttype varchar(10),
tlevel int
)

insert into @t_temp_referenced(tname,ttype,tlevel)
select a.referenced_entity_name,b.xtype,0 from
(
select distinct referenced_entity_name from sys.sql_expression_dependencies
where referencing_id = object_id(@v_name) and [referenced_class] = 1 and [is_ambiguous] = 0
) a
left join sys.sysobjects b on  a.referenced_entity_name=b.name

declare @name varchar(50)
declare @level int
set @level=0

while (select count(1) from @t_temp_referenced where ttype='V' and tlevel=@level) >0
begin
declare result cursor local for select tname from @t_temp_referenced where ttype='V' and tlevel=@level
open result
fetch next from result into @name
while @@fetch_status=0
begin
insert into @t_temp_referenced(tname,ttype,tlevel)
select a.referenced_entity_name,b.xtype,@level+1 from
(
select distinct  referenced_entity_name from sys.sql_expression_dependencies
where referencing_id = object_id(@name) and [referenced_class] = 1 and [is_ambiguous] = 0
) a
left join sys.sysobjects b on  a.referenced_entity_name=b.name
where not exists(select 1 from @t_temp_referenced t1
where t1.tname=a.referenced_entity_name and t1.ttype=b.xtype and t1.tlevel=@level+1 )

fetch next from result into @name
end

close result
deallocate result

set @level=@level+1
end

select distinct tname from @t_temp_referenced
where ttype='U'

注意：sys.sql_expression_dependencies视图只有sql2008及以上版本才有，sql2005是没有的。
复制sys.sql_expression_dependencies视图的说明如下：

列名	数据类型	说明
referencing_id	int	引用实体的 ID。不可为 Null。
referencing_minor_id	int	引用实体为列时的列 ID；否则为 0。不可为 Null。
referencing_class	tinyint	引用实体的类。 1 = 对象或列 12 = 数据库 DDL 触发器 13 = 服务器 DDL 触发器不可为 Null。
referencing_class_desc	nvarchar(60)	对引用实体的类的说明。 OBJECT_OR_COLUMN DATABASE_DDL_TRIGGER SERVER_DDL_TRIGGER 不可为 Null。
is_schema_bound_reference	bit	1 = 被引用的实体绑定到架构。 0 = 被引用的实体未绑定到架构。不可为 Null。
referenced_class	tinyint	被引用的实体的类。 1 = 对象或列 6 = 类型 10 = XML 架构集合 21 = 分区函数不可为 Null。
referenced_class_desc	nvarchar(60)	对被引用的实体的类的说明。 OBJECT_OR_COLUMN TYPE XML_SCHEMA_COLLECTION PARTITION_FUNCTION 不可为 Null。
referenced_server_name	sysname	被引用的实体的服务器的名称。此列是为通过指定由四个部分组成的有效名称所生成的跨服务器依赖关系填充的。有关由多个部分组成的名称的信息。对于非绑定到架构的实体，如果实体被引用时没有指定由四个部分组成的名称，此列为 NULL。对于绑定到架构的实体，此列为 NULL，原因在于它们必须在同一数据库中，因而只能使用由两个部分 (schema.object) 组成的名称定义。
referenced_database_name	sysname	被引用的实体的数据库的名称。此列是为通过指定由三个部分或四个部分组成的有效名称生成的跨数据库或跨服务器引用填充的。对于非绑定到架构的引用，当使用由一个部分或两个部分组成的名称指定时，此列为 NULL。对于绑定到架构的实体，此列为 NULL，原因在于它们必须在同一数据库中，因而只能使用由两个部分 (schema.object) 组成的名称定义。
referenced_schema_name	sysname	被引用的实体所属的架构。对于非绑定到架构的引用，如果实体被引用时没有指定架构名称，此列为 NULL。对于绑定到架构的引用，此列永不为 NULL，原因在于必须使用由两部分组成的名称来定义和引用绑定到架构的实体。
referenced_entity_name	sysname	被引用的实体的名称。不可为 Null。
referenced_id	int	被引用的实体的 ID。对于跨服务器和跨数据库引用，此列始终为 NULL。对于数据库内的引用，如果无法确定 ID，则为 NULL。对于非绑定到架构的引用，在以下情况下将无法解析 ID：被引用实体不存在于数据库中。被引用的实体的架构依赖于调用方的架构，并在运行时解析。在这种情况下，is_caller_dependent 设置为 1。对于绑定到架构的引用，此列永远不会为 NULL。
referenced_minor_id	int	引用实体为列时被引用的列的 ID；否则为 0。不可为 Null。当列在引用实体中按名称标识时，或者当 SELECT * 语句中使用了父实体时，被引用的实体为列。
is_caller_dependent	bit	指示被引用的实体的架构绑定在运行时发生，因此，实体 ID 的解析依赖于调用方的架构。当被引用的实体为存储过程、扩展存储过程或在 EXECUTE 语句中调用的非绑定到架构的用户定义函数时，将会出现这种情况。 1 = 被引用的实体依赖于调用方并在运行时解析。在这种情况下，referenced_id 为 NULL。 0 = 被引用的实体 ID 不依赖调用方。对于绑定到架构的引用、显式指定架构名称的跨数据库和跨服务器的引用，始终为 0。例如，对格式为 EXEC MyDatabase.MySchema.MyProc 的实体的引用不依赖于调用方。但是，格式为 EXEC MyDatabase..MyProc 的引用依赖调用方。
is_ambiguous	bit	指示引用为不明确引用，并可以在运行时解析为用户定义函数、用户定义类型 (UDT) 或者对 xml 类型的列的 xquery 引用。例如，假定语句 SELECT Sales.GetOrder() FROM Sales.MySales 是在存储过程中定义的。在执行存储过程之前，并不知道 Sales.GetOrder() 是 Sales 架构中的用户定义函数还是带有名为 GetOrder() 的方法、类型为 UDT 且名为 Sales 的列。 1 = 引用不明确。 0 = 引用是明确的，或者在调用视图时可以成功绑定实体。对于绑定到架构的引用始终为 0。