SQL2k/2005查询表的结构信息

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 [表名],[列名]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16998571/viewspace-605791/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16998571/viewspace-605791/