计算机等级考试辅导：SQL2k/2005查询表的结构信息

　　SQL2000下执行语句：
　　SELECT
　　sysobjects.name AS [表名],
　　sysproperties.[value] AS [表说明],
　　syscolumns.name AS [列名],
　　properties.[value] AS [字段说明],
　　systypes.name AS [字段类型],
　　syscolumns.length AS [长度],
　　ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0) AS [小数位数],
　　CASE syscolumns.isnullable WHEN '1' THEN 'Y' ELSE 'N' END AS [是否允许空值],
　　CASE WHEN syscomments.text IS NULL THEN '' ELSE syscomments.text END AS [默认值],
　　CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS [自增标识],
　　CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name IN
　　(SELECT name
　　FROM sysindexes
　　WHERE indid IN
　　(SELECT indid
　　FROM sysindexkeys
　　WHERE id = syscolumns.id AND colid = syscolumns.colid)))
　　THEN '√' ELSE '' END AS [主键] #
　　FROM syscolumns INNER JOIN
　　sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
　　systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
　　sysproperties properties ON syscolumns.id = properties.id AND
　　syscolumns.colid = properties.smallid LEFT OUTER JOIN
　　sysproperties ON sysobjects.id = sysproperties.id AND
　　sysproperties.smallid = 0 LEFT OUTER JOIN
　　syscomments ON syscolumns.cdefault = syscomments.id
　　WHERE (sysobjects.xtype = 'U')
　　--AND sysobjects.NAME='表名' --只查询某个表的结构,否则将本行注释掉就是全库
　　ORDER BY [表名],[列名]
　　--SQL2005下执行语句：
　　SELECT
　　Sysobjects.name AS [表名],
　　syscolumns.name AS [列名],
　　systypes.name AS [数据类型],
　　syscolumns.length AS [字段长度],
　　sys.extended_properties.[value] AS [字段描述],
　　syscomments.text AS [默认值],
　　CASE syscolumns.isnullable WHEN '1' THEN 'Y' ELSE 'N' END AS [是否允许空值],

#

　　ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0) AS [小数位数],
　　COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity') AS [自增标识] ,
　　CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name IN
　　(SELECT name
　　FROM sysindexes
　　WHERE indid IN
　　(SELECT indid
　　FROM sysindexkeys
　　WHERE id = syscolumns.id AND colid = syscolumns.colid)))
　　THEN '√' ELSE '' END AS [主键]
　　FROM syscolumns
　　INNER JOIN systypes
　　ON syscolumns.xtype = systypes.xtype
　　LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
　　LEFT OUTER JOIN sys.extended_properties ON
　　( sys.extended_properties.minor_id = syscolumns.colid
　　AND sys.extended_properties.major_id = syscolumns.id)
　　LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
　　WHERE (systypes.name <> 'sysname')
　　-- AND syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = 'U' AND NAME = '表名') --仅查某表，否则注释掉本行为全库

#

　　ORDER BY [表名],[列名] #