【原】獲取SQLServer的最完整資料字典的SQL語句

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