SELECT

sysobjects.name AS 表名稱(chēng) , 
--sysproperties.[value] AS 表說(shuō)明 ,
syscolumns.name AS 字段名稱(chēng) , 
--properties.[value] AS 字段說(shuō)明 ,
systypes.name AS 字段類(lèi)型 ,
syscolumns.length AS 字段長(zhǎng)度 , 
ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,'Scale'), 0) AS 小數(shù)位數(shù) ,

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 索引名稱(chēng) , 
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 索引類(lèi)型 , 
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                            -- 數(shù)據(jù)表字段 
INNER JOIN sysobjects                        -- 數(shù)據(jù)對(duì)象 
  ON sysobjects.id = syscolumns.id 
INNER JOIN systypes                         -- 數(shù)據(jù)類(lèi)型 
  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                  -- 數(shù)據(jù)庫(kù) 索引表 
  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