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 WHEN syscolumns.isnullable=0  THEN '' ELSE '√' 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 sysindexes.name IS NULL THEN '' ELSE sysindexes.name END AS 索引名称,
CASE WHEN sysindexkeys.keyno IS NULL THEN '' ELSE  CONVERT(VARCHAR(10),sysindexkeys.keyno ) END AS 索引位置,
CASE WHEN sysindexes.indid=1 THEN '聚集索引' 
           WHEN sysindexes.indid>1 AND sysindexes.indid<>255 THEN '非聚集索引'
           WHEN sysindexes.indid IS NULL 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 主键,
CASE WHEN sysforeignkeys.constid IS NULL 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
LEFT OUTER JOIN sysindexkeys --索引中的键或列的信息
      ON sysindexkeys.id = syscolumns.id  AND sysindexkeys.colid = syscolumns.colid
LEFT OUTER JOIN sysindexes  --数据库索引表
      ON sysindexes.id = sysindexkeys.id AND sysindexes.indid = sysindexkeys.indid
LEFT OUTER JOIN sysforeignkeys --數據庫索引主鍵表
      ON sysforeignkeys.fkeyid = syscolumns.id  AND sysforeignkeys.fkey = syscolumns.colid
WHERE (sysobjects.xtype = 'U')
order by sysobjects.id,syscolumns.colid

posted on 2008-11-17 10:56  Jimmyzhang  阅读(719)  评论()    收藏  举报