經典 SQL Server 資料字典查詢語句：單一查詢掌握表結構

若只需一條 SQL 即可取得資料表結構、欄位說明、資料類型、長度、精度、小數位、是否允許 Null、預設值、識別欄位與主鍵標記，以下經典查詢語句即可滿足需求。適用於 SQL Server 2000/2005，開發人員可依專案實際情況調整欄位或加入過濾條件。

SELECT
    sysobjects.name AS [table],
    sysproperties.[value] AS 表說明,
    syscolumns.name AS field,
    properties.[value] AS 字段說明,
    systypes.name AS type,
    syscolumns.length,
    ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0) AS 小數位數,
    syscolumns.isnullable AS isnull,
    CASE WHEN syscomments.text IS NULL THEN '' ELSE syscomments.text END AS [Default],
    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 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
WHERE sysobjects.xtype = 'U';

說明：查詢透過系統表 syscolumns、sysobjects、systypes、sysproperties、syscomments 串聯，並以 COLUMNPROPERTY 函數判斷識別欄位與精度，子查詢檢查主鍵狀態。若需支援 SQL Server 2008 以上版本，建議改用 sys.tables、sys.columns、sys.extended_properties 等新版目錄檢視。