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

近日用到查询视图字段对应基表字段还是会有错位。 

因为实际用到的比较少，几年前发布的基于动态视图实现的方法， 就不作深入研究啦。

重新发布查询指定的单个视图名的存储过程，经测试无误！

CREATE proc [dbo].[pGetViewSchema]
@ViewName nvarchar(50)
as
declare @vid nvarchar(50);declare @tid nvarchar(50);declare @tName nvarchar(50);
select @vid=id from sysobjects where name=@ViewName 
select top 1 @tid=depid from sysdepends where id=@vid 
select @tName=name from sysobjects where id=@tid

select @ViewName ViewName,@tName TableName
    ,t1.name ViewCol,t2.name TableCol
from (select name,ROW_NUMBER() over(order by colid) as a 
    from syscolumns where id=@vid)     t1,
(select name,ROW_NUMBER() over(order by colid) as a 
    from syscolumns where id=@tid and colid in
(select depnumber from sysdepends where id=@vid))   t2
where t1.a=t2.a

 

---

---

 

 

以下是几年前发的动态视图的方法：

Create View [dbo].[ViewSchema]
as
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

 

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

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