急问 怎么找出视图字段对应的表字段，谢谢！！！

[wintle]

尝试了好久，也在网上查了不少的资料，csdn上也有不少曾经讨论过这个问题，但似乎没有一个好的解决方法。只有一层的自然好说（但也有问题），现在我想通过sql和c#程序结合起来，通过递归的方法找，因为在我理解中的视图的层次关系大体还是类似于树的结构，所以理论上可以历遍的，现在的一个问题是，sysdepends系统中似乎不能完整反映这种层次依赖关系，例如由表A、视图B、视图C、表D组成的视图E，在sysdepends表中有可能就只记录了视图E对视图B，表A，视图C的依赖关系，而没有反映出和表D的关系，这一点真是比较令我感到奇怪。

谁有高招，请教教在下哈！！！

我想达到的目标也就是对着一个视图，可以精确的分析出这个视图由哪些对象（表、视图）组成，同时，这个视图的每个字段都和哪个对象的哪个字段对应着的。

如果可以实现这个目标，那么，我想只要通过递归的方法，就可以一层一层地分析下去了，如果是表，那么就停，如果是视图，再分析进去，总可以找到，最上层这个视图的某个字段到底是由哪个基表提供的。

谢谢帮忙！！！

[wintle]

谢谢邹建，其实之前我已经参考过你回的一些关于这方面的贴子了。这样，我写出我现在的代码，你帮忙看看吧。

我的最后目标是要得到视图字段的中文说明（即sysproperties.value）。

我是这样做的：

通过这句得到视图的各种信息：
sql = "SELECT dbo.syscolumns.id, dbo.syscolumns.colid, dbo.syscolumns.name, dbo.systypes.name AS type, dbo.syscolumns.length, dbo.syscolumns.isnullable, dbo.syscolumns.xprec, dbo.syscolumns.xscale, dbo.syscomments.text, dbo.sysproperties.[value],objectproperty(dbo.sysdepends.depid, 'IsView') as isView,dbo.sysdepends.depid FROM dbo.syscolumns col INNER JOIN dbo.sysdepends ON col.id = dbo.sysdepends.depid AND col.colid = dbo.sysdepends.depnumber RIGHT OUTER JOIN dbo.syscolumns ON col.name = dbo.syscolumns.name AND dbo.sysdepends.id = dbo.syscolumns.id INNER JOIN dbo.systypes ON dbo.syscolumns.xusertype = dbo.systypes.xusertype LEFT OUTER JOIN dbo.syscomments ON dbo.syscolumns.cdefault = dbo.syscomments.id LEFT OUTER JOIN dbo.sysproperties ON col.id = dbo.sysproperties.id AND col.colid = dbo.sysproperties.smallid WHERE (dbo.syscolumns.id = OBJECT_ID('"+tbName+"')) AND (dbo.syscolumns.name <> 'dtproperties') ORDER BY dbo.syscolumns.colid";
其中tbName为视图的名称。

第二步：
在由上面sql结果给字段集合赋值的时候，这样执行：

col.CHName=GetViewColumnsCHName(dbName,col.Name,reader["id"].ToString());

GetViewColumnsCHName(string dbName,string tbName,string viewId)的目标是一直追出这个tbName视图的dbName字段到底是基于谁的。代码如下，是一个自调用的递归：

/// <summary>
/// 得到视图字段依赖源表的字段的中文说明。
/// </summary>
/// <param name="dbName">数据库名</param>
/// <param name="name">字段名称</param>
/// <param name="viewId">视图Id</param>
/// <returns></returns>
private string GetViewColumnsCHName(string dbName,string name,string viewId)
{
string reval = string.Empty;
Database data = new Database();
data.DatabaseName=dbName;
SqlDataReader reader = null;
string sql = "SELECT dbo.syscolumns.name, dbo.sysproperties.[value], OBJECT_NAME(dbo.syscolumns.id) AS tbName, dbo.syscolumns.id FROM dbo.sysproperties RIGHT OUTER JOIN dbo.syscolumns ON dbo.sysproperties.id = dbo.syscolumns.id AND dbo.sysproperties.smallid = dbo.syscolumns.colid RIGHT OUTER JOIN dbo.sysdepends ON dbo.syscolumns.id = dbo.sysdepends.depid AND dbo.syscolumns.colid = dbo.sysdepends.depnumber WHERE (dbo.sysdepends.id ="+viewId+") AND (dbo.syscolumns.name = '"+name+"')";
data.RunCommand(sql,out reader);
if(reader.Read())
{
if(!this.IsView(dbName,reader["tbName"].ToString()))
{
reval = reader["value"].ToString();
}
else
{
reval = this.GetViewColumnsCHName(dbName,name,reader["id"].ToString());
}
}
reader.Close();
reader = null;
data.Close();
data.Dispose();
return reval;

}

this.IsView(string dbName,string tbName)是一个判断tbName是否是视图的函数。

谢谢指导！!

[wintle]

发现，sysdepends没有指出视图与其基表（视图）之间的字段对应关系，而只是指出了视图（整体）是依赖于哪些基的哪些字段。所以没有办法，只有考虑用字段名称来做一个对应的约束，但这种方法有很多不足之处，不过就我所知，已经是最大限度令能找出了。
不知你是否还有更好的建议！谢谢。

[zjcxc]

因为会出现一个基表字段对应多个视图字段.
或者多个基表字段对应一个视图字段等情况,所以 sysdepends 无法记录其对应关系

[wintle]

那到底有没有办法得到视图对应的基表字段啊...

[zjcxc]

不能完全得到.

[popsn]

不懂。

[bflovesnow]

select * from information_schema.views

[bflovesnow]

错了，应该是 information_schema.view_column_usage 视图：

select * from information_schema.view_column_usage

[bflovesnow]

如果是基于 视图 的 视图，“递归”即可

[wintle]

你好北风，我不知道是我的视图问题还是view_column_usage的问题，你看看我这个视图：
CREATE VIEW dbo.BSStudentAll
AS
SELECT dbo.BSStudent.StuNo, dbo.BSStudent.Name, dbo.BSStudent.NickName,
dbo.BSStudent.PassWord, dbo.BSStudent.Sex, dbo.BSStudent.Addr,
dbo.BSStudent.Phone, dbo.BSStudent.HomeProvince, dbo.BSStudent.HomeCity,
dbo.BSStudent.HomeAddr, dbo.BSStudent.HomePhone,
dbo.BSStudent.HomePostalCode, dbo.BSStudent.HomeType,
dbo.BSStudent.BaseFrom, dbo.BSStudent.HomeNumber,
dbo.BSStudent.HomeIncome, dbo.BSStudent.HomeIncomePer,
dbo.BSStudent.HomeIncomeFrom, dbo.BSStudent.GraduateShool,
dbo.BSStudent.BeginDate, dbo.BSStudent.BeginScore, dbo.BSStudent.ClassOrder,
dbo.BSStudent.ClassID, dbo.BSStudent.Nation, dbo.BSStudent.Birthday,
dbo.BSStudent.Party, dbo.BSStudent.ICN, dbo.BSStudent.Email,
dbo.BSStudent.Health, dbo.BSStudent.ActiveSocialWork, dbo.BSStudent.Hobby,
dbo.BSStudent.Specialism, dbo.BSStudent.Mobile, dbo.BSStudent.PhotoUrl,
dbo.BSStudent.StarMoney, dbo.BSStudent.Status, dbo.BSStudent.StudyStatus,
dbo.BSStudent.NowAtWhere, dbo.BSStudent.LastLoginIP,
dbo.BSStudent.LastLoginDate, dbo.BSStudent.LastReNewPWD,
dbo.BSStudent.ReNewPWDIntervalDay, dbo.BSStudent.ForceReNewPWD,
dbo.BSStudent.GetBackPwdQuestion, dbo.BSStudent.GetBackPwdAnswer,
dbo.BSClass.Grade, dbo.BSClass.ClassNumber, dbo.BSClass.IsGraduated,
dbo.BSMajor.Name AS MajorName, dbo.BSMajor.ShortName,
dbo.BSMajor.StudyTime, dbo.BSMajor.Type, dbo.BSMajor.Code,
dbo.BSCollege.Name AS CollegeName,
dbo.BSCollege.ShortName AS CollegeShortName,
dbo.BSUniversity.Name AS UniversityName,
dbo.BSUniversity.Type AS UniversityType, dbo.BSUniversity.Addr AS UniversityAddr,
dbo.BSUniversity.PostalCode AS UniversityPostalCode,
dbo.BSUniversity.Code AS UniversityCode,
dbo.BSUniversity.Accounts AS UniversityAccounts,
dbo.BSUniversity.Bank AS UniversityBank,
dbo.BSUniversity.BankKind AS UniversityBankKind
FROM dbo.BSClass INNER JOIN
dbo.BSMajor ON dbo.BSClass.MajorID = dbo.BSMajor.ID INNER JOIN
dbo.BSCollege ON dbo.BSMajor.CollegeID = dbo.BSCollege.ID INNER JOIN
dbo.BSStudent ON dbo.BSClass.ID = dbo.BSStudent.ClassID INNER JOIN
dbo.BSUniversity ON dbo.BSCollege.UniversityId = dbo.BSUniversity.ID

然后用
select * from information_schema.view_column_usage where view_name='bsstudentall'

只得到这下内容：
ScuSa dbo BSStudentAll ScuSa dbo BSCollege ID
ScuSa dbo BSStudentAll ScuSa dbo BSCollege Name
ScuSa dbo BSStudentAll ScuSa dbo BSCollege ShortName
ScuSa dbo BSStudentAll ScuSa dbo BSCollege UniversityId
ScuSa dbo BSStudentAll ScuSa dbo BSUniversity ID
ScuSa dbo BSStudentAll ScuSa dbo BSUniversity Name
ScuSa dbo BSStudentAll ScuSa dbo BSUniversity Type
ScuSa dbo BSStudentAll ScuSa dbo BSUniversity Addr
ScuSa dbo BSStudentAll ScuSa dbo BSUniversity PostalCode
ScuSa dbo BSStudentAll ScuSa dbo BSUniversity Code
ScuSa dbo BSStudentAll ScuSa dbo BSUniversity Accounts
ScuSa dbo BSStudentAll ScuSa dbo BSUniversity Bank
ScuSa dbo BSStudentAll ScuSa dbo BSUniversity BankKind
ScuSa dbo BSStudentAll ScuSa dbo BSMajor ID
ScuSa dbo BSStudentAll ScuSa dbo BSMajor Name
ScuSa dbo BSStudentAll ScuSa dbo BSMajor ShortName
ScuSa dbo BSStudentAll ScuSa dbo BSMajor StudyTime
ScuSa dbo BSStudentAll ScuSa dbo BSMajor Type
ScuSa dbo BSStudentAll ScuSa dbo BSMajor Code
ScuSa dbo BSStudentAll ScuSa dbo BSMajor CollegeID
ScuSa dbo BSStudentAll ScuSa dbo BSClass ID
ScuSa dbo BSStudentAll ScuSa dbo BSClass MajorID
ScuSa dbo BSStudentAll ScuSa dbo BSClass Grade
ScuSa dbo BSStudentAll ScuSa dbo BSClass ClassNumber
ScuSa dbo BSStudentAll ScuSa dbo BSClass IsGraduated


居然没有BSStudent 表的内容，真是奇怪，而且在sysdepends里也没有。不知道是不是sqlserver的问题啊。。。

[zjcxc]

你的视图的基表 BSStudent 已经被删除了吧? 如果基表被删除,就有这样的问题.

[wintle]

太奇怪了，我把视图改成：
CREATE VIEW dbo.BSStudentAll
AS
SELECT dbo.BSStudent.StuNo, dbo.BSStudent.Name, dbo.BSStudent.NickName,
dbo.BSStudent.PassWord, dbo.BSStudent.Sex, dbo.BSStudent.Addr,
dbo.BSStudent.Phone, dbo.BSStudent.HomeProvince, dbo.BSStudent.HomeCity,
dbo.BSStudent.HomeAddr, dbo.BSStudent.HomePhone,
dbo.BSStudent.HomePostalCode, dbo.BSStudent.HomeType,
dbo.BSStudent.BaseFrom, dbo.BSStudent.HomeNumber,
dbo.BSStudent.HomeIncome, dbo.BSStudent.HomeIncomePer,
dbo.BSStudent.HomeIncomeFrom, dbo.BSStudent.GraduateShool,
dbo.BSStudent.BeginDate, dbo.BSStudent.BeginScore, dbo.BSStudent.ClassOrder,
dbo.BSStudent.ClassID, dbo.BSStudent.Nation, dbo.BSStudent.Birthday,
dbo.BSStudent.Party, dbo.BSStudent.ICN, dbo.BSStudent.Email,
dbo.BSStudent.Health, dbo.BSStudent.ActiveSocialWork, dbo.BSStudent.Hobby,
dbo.BSStudent.Specialism, dbo.BSStudent.Mobile, dbo.BSStudent.PhotoUrl,
dbo.BSStudent.StarMoney, dbo.BSStudent.Status, dbo.BSStudent.StudyStatus,
dbo.BSStudent.NowAtWhere, dbo.BSStudent.LastLoginIP,
dbo.BSStudent.LastLoginDate, dbo.BSStudent.LastReNewPWD,
dbo.BSStudent.ReNewPWDIntervalDay, dbo.BSStudent.ForceReNewPWD,
dbo.BSStudent.GetBackPwdQuestion, dbo.BSStudent.GetBackPwdAnswer,
dbo.BSClass.Grade, dbo.BSClass.ClassNumber, dbo.BSClass.IsGraduated,
dbo.BSMajor.Name AS MajorName, dbo.BSMajor.ShortName,
dbo.BSMajor.StudyTime, dbo.BSMajor.Type, dbo.BSMajor.Code,
dbo.BSCollege.Name AS CollegeName,
dbo.BSCollege.ShortName AS CollegeShortName,
dbo.BSUniversity.Name AS UniversityName,
dbo.BSUniversity.Type AS UniversityType, dbo.BSUniversity.Addr AS UniversityAddr,
dbo.BSUniversity.PostalCode AS UniversityPostalCode,
dbo.BSUniversity.Code AS UniversityCode,
dbo.BSUniversity.Accounts AS UniversityAccounts,
dbo.BSUniversity.Bank AS UniversityBank,
dbo.BSUniversity.BankKind AS UniversityBankKind
FROM dbo.BSUniversity INNER JOIN
dbo.BSCollege INNER JOIN
dbo.BSMajor ON dbo.BSCollege.ID = dbo.BSMajor.CollegeID ON
dbo.BSUniversity.ID = dbo.BSCollege.UniversityId INNER JOIN
dbo.BSClass INNER JOIN
dbo.BSStudent ON dbo.BSClass.ID = dbo.BSStudent.ClassID ON
dbo.BSMajor.ID = dbo.BSClass.MajorID

[wintle]

就又可以了。。。

[zjcxc]

--测试表
create table aaa(id int)
create table bbb(id int)
go

--测试视图
create view abc
as
select * from aaa
union all
select * from bbb
go

--显示视图构
select view_name,table_name,column_name
from information_schema.view_column_usage
where view_name='abc'
go

--删除一个表再看结果
drop table bbb
select view_name,table_name,column_name
from information_schema.view_column_usage
where view_name='abc'
go

--删除测试
drop table aaa
drop view abc

/*--测试结果

view_name table_name column_name
---------- -------------- -----------------
abc aaa id
abc bbb id

（所影响的行数为 2 行）


view_name table_name column_name
---------- -------------- -----------------
abc aaa id

（所影响的行数为 1 行）

--*/

[wintle]

谢谢，我只是调整了一下视图中join的先后顺序，就又全部显示出来了，不理解，基表是存在的。

另，如果视图中字段采用了别名，是不是就没有办法知道到底依赖的基表哪个字段了呀？

例如以上，我把College的Name用别名CollegeName来代替了，主要是为了和学生姓名(Name)区别开来，而这个时候，我就没有办法直接用字段名字去做为条件搜索基表了。有没有办法解决？

[wintle]

有没有办法？

[zjcxc]

我想应该是没有什么办法的了.

[wintle]

那只有自己分析create view 语句了，这个里面如：dbo.BSMajor.Name AS MajorName，还是很有规则的，应该很容易分析吧。只可惜这样来分析真的效果不高。不知道在sql server2005里面怎么样，会不会解决这个问题。hehe ..

[wintle]

谢谢邹建和北风以及所有关注这个问题的朋友了：）
我通过c#做了一个不划算，但至少可以解决问题的方法，放在我在csdn的blog里了，有兴趣的朋友可以看看：)
我的blog:在
http://blog.csdn.net/wintle/

结贴。
谢谢各位。

[gucs]

学习！