首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

求一与系统表相关的SQL语句,马上结贴

2012-02-20 
求一与系统表相关的SQL语句,在线等,马上结贴.表的描述可以从系统表里面取得.请问视图的描述如何从系统表里

求一与系统表相关的SQL语句,在线等,马上结贴.
表的描述可以从系统表里面取得.请问视图的描述如何从系统表里面读出来.分不够再加.

[解决办法]
幫你頂下。等下有空看看
[解决办法]

select * from sysobjects where xtype= 'v '
[解决办法]
在企业管理—表—设计表
[解决办法]
select * from sysobjects where xtype= 'v '--获得所有试图的基本信息
select * from syscolumns where id=object_id( '视图名字 ')--获得指定试图的列信息
[解决办法]



select b.* from sysobjects a join syscolumns b
on a.id = b.id
where a.xtype= 'v ' and a.name = 'tableName '



[解决办法]

select a.TableName, a.FieldName,
a.FieldType, a.FieldTypeName,
a.FieldWidth,a.xprec,
a.FieldDigits,a.name,
isnull(a.[value],b.value)FieldCaption, a.id, a.smallid,
a.xtype
from(
SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS FieldName,
dbo.syscolumns.xtype AS FieldType, dbo.systypes.name AS FieldTypeName,
dbo.syscolumns.length AS FieldWidth, dbo.syscolumns.xprec,
dbo.syscolumns.xscale AS FieldDigits, dbo.sysproperties.name,
dbo.sysproperties.[value], dbo.sysproperties.id, dbo.sysproperties.smallid,
dbo.sysobjects.xtype
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype LEFT OUTER JOIN
dbo.sysproperties ON dbo.syscolumns.id = dbo.sysproperties.id AND
dbo.syscolumns.colid = dbo.sysproperties.smallid
WHERE (dbo.sysobjects.xtype = 'U ') OR
(dbo.sysobjects.xtype = 'V ')
)A
Left Join
(SELECT distinct viewname,fieldname,value
FROM (SELECT dbo.sysobjects.id AS ViewID, dbo.sysobjects.name AS ViewName,
sysobjects_1.id AS TableID, sysobjects_1.name AS TableName,
dbo.syscolumns.colid AS FieldIndex,
dbo.syscolumns.name AS FieldName
FROM dbo.sysobjects INNER JOIN
dbo.sysdepends ON dbo.sysobjects.id = dbo.sysdepends.id INNER JOIN
dbo.syscolumns ON dbo.sysdepends.depnumber = dbo.syscolumns.colid AND
dbo.sysdepends.depid = dbo.syscolumns.id INNER JOIN
dbo.sysobjects sysobjects_1 ON
dbo.syscolumns.ID = sysobjects_1.ID
WHERE (dbo.sysobjects.xtype = 'V ')) A INNER JOIN
dbo.sysproperties ON A.TableID = dbo.sysproperties.id AND
A.FieldIndex = dbo.sysproperties.smallid)B
on a.TableName=b.viewName and a.fieldname=b.FieldName

热点排行