查询视图对应的基表名以及视图字段和对应的基表字段名

转：https://blog.****.net/weixin_33845477/article/details/93364024

select v.name ViewName,t.name TableName,vc.name ViewCol,tc.name TableCol
from sysdepends d,
     sysobjects v,
     syscolumns vc,
     sysobjects t,
     syscolumns tc
where objectproperty(d.id,'IsView')=1 
    and d.id=v.id 
    and v.id=vc.id
    and d.depnumber=vc.colid  
    and d.depnumber=tc.colid  
    and d.depid=t.id 
    and t.id=tc.id
    --and v.name='V_AMeals'
order by v.name

以上，查询视图对应的基表名以及视图字段和对应的基表字段名。

注意：视图中必须有主键字段，否则查出的对应关系可能会错乱！！！

 

--select *,object_name(id) as 表名 ,c.name as 字段名 ,t.name 数据类型 ,c.prec as 长度  
--from syscolumns c inner join systypes t on c.xusertype=t.xusertype 
--where objectproperty(c.id,'IsView')=1 and c.id=object_id('V_AMeals')

--获取视图时段对应的基表

--drop table #t
select * into #t from
(
select v.name ViewName,t.name TableName,vc.name ViewCol,tc.name TableCol
from sysdepends d,
     sysobjects v,
     syscolumns vc,
     sysobjects t,
     syscolumns tc
where objectproperty(d.id,'IsView')=1 
    and d.id=v.id 
    and v.id=vc.id
    and d.depnumber=vc.colid  
    and d.depnumber=tc.colid  
    and d.depid=t.id 
    and t.id=tc.id
    and v.name='V_AMeals'
    --order by v.name
) as dd

select * from #t

--获取视图对应多表的所有字段
select sc.name as TableCol,st.name as TableName from syscolumns as sc,sysobjects as st
where sc.id=st.id
and st.name in (select TableName
from   #t
 )  
--获取视图对应的表名
select distinct TableName from   #t
--select Table_Name from INFORMATION_SCHEMA.VIEW_TABLE_USAGE where View_Name='V_AMeals'

 

--获取视图字段在子表中重复的字段
select TableCol,count(0) from
(
select sc.name as TableCol,st.name as TableName from syscolumns as sc,sysobjects as st
where sc.id=st.id
and st.name in (select TableName
from   #t
 )  
 ) as ss
 group by TableCol
 having count(0)>1