比如
create view v_a
as
select a.*,b.*,c.*
from t1 a,t2 b,v_v1 c
where a.id=b.id and b.id=c.id 然后我要查出结果为
t1,
t2,
v_v1
三列结果。其中t1,t2是基础表,v_v1是视图
create view v_a
as
select a.*,b.*,c.*
from t1 a,t2 b,v_v1 c
where a.id=b.id and b.id=c.id 然后我要查出结果为
t1,
t2,
v_v1
三列结果。其中t1,t2是基础表,v_v1是视图
这个我测试了,不完整,不知道要满足什么条件sys.sql_dependencies中才会有记录。反正是有些有,有些没有。
where referencing_id = object_id(N'v_a')
AND [referenced_class] = 1
and [is_ambiguous] = 0
v.name '视图的名称', o.name '这个视图引用到的对象名称' from sys.views v
inner join sys.sql_dependencies d
on d.object_id = v.object_id
inner join sys.objects o
on o.object_id = d.referenced_major_id
where v.name = 'v_a' --视图名称
v.name '视图的名称', o.name '这个视图引用到的对象名称' from sys.views v
inner join sys.sql_dependencies d
on d.object_id = v.object_id
inner join sys.objects o
on o.object_id = d.referenced_major_id
where v.name = 'v_a' --视图名称这个不行啊,视图中的视图查不出来。
v.name '视图的名称', o.name '这个视图引用到的对象名称' from sys.views v
inner join sys.sql_dependencies d
on d.object_id = v.object_id
inner join sys.objects o
on o.object_id = d.referenced_major_id
where v.name = 'v_a' --视图名称这个不行啊,视图中的视图查不出来。
如果你想查出视图中引用的视图中的表用 sql_dependencies 是不行的,单层的依赖。
v.name '视图的名称', o.name '这个视图引用到的对象名称' from sys.views v
inner join sys.sql_dependencies d
on d.object_id = v.object_id
inner join sys.objects o
on o.object_id = d.referenced_major_id
where v.name = 'v_a' --视图名称这个不行啊,视图中的视图查不出来。你是想查询到v_a这个视图中的这个视图v_v1,引用了哪些对象是吧。下面的可以,不仅能显示v_v1,还能显示v_v1引用的对象。WITH t --递归查询
AS
(
SELECT object_id,
referenced_major_id,
0 AS level
FROM sys.sql_dependenciesUNION ALLSELECT t.object_id,
d.referenced_major_id,
LEVEL + 1 AS level
FROM t
INNER JOIN sys.sql_dependencies d
ON t.referenced_major_id = d.object_id
)
select distinct
v.name '视图的名称', o.name '这个视图引用到的对象名称' from sys.views v
inner join t d
on d.object_id = v.object_id
inner join sys.objects o
on o.object_id = d.referenced_major_id
where v.name = 'v_a' --视图名称
使用sql命令查询视图中所有引用的基础表
写的复杂了些。