Sql语句：显⽰SqlServer中所有表中的信息显⽰某个Sql Server某个数据库中所有表或视图的信息
sql server 2000 与 2005 不同差别在于红⾊字部分
以下语句为获取所有表信息，替换绿⾊⿊体字"U"为"V"为获取所有视图信息。
Sql Server 2000 版本
SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME, systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH, sysproperties.[value] AS COLUMN_DESCRIPTION,  as
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns
INNER JOIN systypes
pe = pe
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
LEFT OUTER JOIN sysproperties ON
sql语句替换表中内容
( sysproperties.smallid = lid
AND sysproperties.id = syscolumns.id)
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
WHERE syscolumns.id IN
(SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')
ORDER lid
Sql Server 2005版本
SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME, systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns
INNER JOIN systypes
pe = pe
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
LEFT OUTER ded_properties ON
( ded_properties.minor_id = lid
ded_properties.major_id = syscolumns.id)
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
WHERE syscolumns.id IN
(SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')
ORDER lid
参考：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。