一條 SQL 查詢取得所有資料表欄位資訊：名稱、類型、長度、描述、主鍵判斷

適用場景

開發資料庫匯出 Excel、產生文件、程式碼產生器等工具時，需要一次性取得所有使用者資料表的欄位完整定義。

完整 T-SQL 查詢

SELECT 
    Sysobjects.Name AS Tb_name,
    Syscolumns.Name AS Col_name,
    Systypes.Name AS Col_type,
    Syscolumns.Length AS Col_len,
    ISNULL(Sysproperties.Value, Syscolumns.Name) AS Col_memo,
    CASE WHEN Syscolumns.Name IN (
        SELECT A.Name
        FROM Syscolumns A
        INNER JOIN Sysobjects B ON A.Id = B.Id AND B.xtype = 'U' AND B.Name <> 'dtproperties'
        WHERE EXISTS (
            SELECT 1 FROM Sysobjects 
            WHERE xtype = 'PK' 
            AND Name IN (
                SELECT Name FROM Sysindexes 
                WHERE Indid IN (
                    SELECT Indid FROM Sysindexkeys 
                    WHERE Id = A.Id AND Colid = A.Colid
                )
            )
        )
        AND B.Name = Sysobjects.Name
    ) THEN 1 ELSE 0 END AS Is_key
FROM Sysobjects, Systypes, Syscolumns
LEFT JOIN Sysproperties ON (Syscolumns.Id = Sysproperties.Id AND Syscolumns.Colid = Sysproperties.Smallid)
WHERE (Sysobjects.xtype = 'U' OR Sysobjects.xtype = 'V')
  AND Sysobjects.Id = Syscolumns.Id
  AND Systypes.xtype = Syscolumns.xtype
  AND Systypes.Name <> 'sysname'
ORDER BY Sysobjects.Name, Syscolumns.Colid;

回傳欄位說明

執行結果範例

補充說明

• 查詢包含使用者資料表（xtype='U'）與檢視表（xtype='V'）。
• 主鍵判斷邏輯：透過 Sysindexes、Sysindexkeys 關聯 Sysobjects（xtype='PK'）反推。
• 相容 SQL Server 2000/2005/2008/2012/2014/2016/2019（系統表結構未大幅變動）。
• 新版本建議改用 INFORMATION_SCHEMA.COLUMNS 與 sys.extended_properties 視圖，語法更標準且支援 Unicode。