一鍵產出 SQL Server 2000/2005 完整資料字典：表結構、索引、鍵、約束、視圖、函數、程序、觸發器

在企業管理員或查詢分析器執行以下查詢，即可完整輸出資料庫所有物件定義：

SELECT TOP 100 PERCENT
    CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
    CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '') ELSE '' END AS 表說明,
    a.colorder AS 字段序號,
    a.name AS 字段名,
    CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 標識,
    CASE WHEN EXISTS (
        SELECT 1 FROM dbo.sysindexes si
        INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
        INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
        INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
        WHERE sc.id = a.id AND sc.colid = a.colid
    ) THEN '√' ELSE '' END AS 主鍵,
    b.name AS 類型,
    a.length AS 長度,
    COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度,
    ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小數位數,
    CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允許空,
    ISNULL(e.text, '') AS 預設值,
    ISNULL(g.[value], '') AS 字段說明,
    d.crdate AS 建立時間,
    CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 修改時間
FROM dbo.syscolumns a
LEFT OUTER JOIN dbo.systypes b ON a.xtype = b.xusertype
INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0
LEFT OUTER JOIN dbo.syscomments e ON a.cdefault = e.id
LEFT OUTER JOIN dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND g.name = 'MS_Description'
LEFT OUTER JOIN dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND f.name = 'MS_Description'
ORDER BY a.id, a.colorder

擴充：其他物件查詢腳本

• 索引：sp_helpindex '表名' 或查 sysindexes/sysindexkeys
• 外鍵：查 sysobjects xtype='F' 結合 sysreferences
• CHECK/DEFAULT/UNIQUE 約束：sysobjects xtype IN ('C','D','UQ')
• 視圖：SELECT * FROM sysobjects WHERE xtype='V'
• 函數：SELECT * FROM sysobjects WHERE xtype IN ('FN','IF','TF')
• 預存程序：SELECT * FROM sysobjects WHERE xtype='P'
• 觸發器：SELECT * FROM sysobjects WHERE xtype='TR'

輸出欄位完整對照

結果可直接複製到 Excel，利用篩選與分頁列印產生專業資料字典文件。相容 SQL Server 2000/2005/2008+。