sql2k:SQL2k/2005查询表的结构信息来源: 发布时间:星期四, 2009年2月12日 浏览:77次 评论:0
-- 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 _disibledevent=>systypes _disibledevent=>sysproperties properties _disibledevent=>syscolumns.colid = properties.smallid LEFT OUTER JOIN sysproperties _disibledevent=>sysproperties.smallid = 0 LEFT OUTER JOIN syscomments _disibledevent=>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 _disibledevent=>LEFT OUTER JOIN sys.extended_properties _disibledevent=>AND sys.extended_properties.major_id = syscolumns.id) LEFT OUTER JOIN syscomments _disibledevent=>WHERE (systypes.name <> 'sysname') -- AND syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = 'U' AND NAME = '表名') --仅查某表 ![]() ORDER BY [表名],[列名] 0
相关文章读者评论发表评论 |