SQL Server 2005 查詢資料表結構與索引的完整腳本

單一查詢取得完整結構描述

以下查詢整合 sys.columns, sys.objects, sys.types, sys.indexes, sys.extended_properties 等目錄檢視，一次輸出：資料表名、說明、欄位序號、欄位名、主鍵標記、Identity、Computed、資料類型、長度、精度、小數位、可空、預設值、欄位說明、索引名、索引排序、建立/修改日期。

SELECT
    TableName       = CASE WHEN C.column_id = 1 THEN O.name ELSE N'' END,
    TableDesc       = ISNULL(CASE WHEN C.column_id = 1 THEN PTB.[value] END, N''),
    Column_id       = C.column_id,
    ColumnName      = C.name,
    PrimaryKey      = ISNULL(IDX.PrimaryKey, N''),
    [IDENTITY]      = CASE WHEN C.is_identity = 1 THEN N'√' ELSE N'' END,
    Computed        = CASE WHEN C.is_computed = 1 THEN N'√' ELSE N'' END,
    Type            = T.name,
    Length          = C.max_length,
    Precision       = C.precision,
    Scale           = C.scale,
    NullAble        = CASE WHEN C.is_nullable = 1 THEN N'√' ELSE N'' END,
    [Default]       = ISNULL(D.definition, N''),
    ColumnDesc      = ISNULL(PFD.[value], N''),
    IndexName       = ISNULL(IDX.IndexName, N''),
    IndexSort       = ISNULL(IDX.Sort, N''),
    Create_Date     = O.create_date,
    Modify_Date     = O.modify_date
FROM sys.columns C
INNER JOIN sys.objects O
    ON C.[object_id] = O.[object_id]
    AND O.type = 'U'
    AND O.is_ms_shipped = 0
INNER JOIN sys.types T
    ON C.user_type_id = T.user_type_id
LEFT JOIN sys.default_constraints D
    ON C.[object_id] = D.parent_object_id
    AND C.column_id = D.parent_column_id
    AND C.default_object_id = D.[object_id]
LEFT JOIN sys.extended_properties PFD
    ON PFD.class = 1
    AND C.[object_id] = PFD.major_id
    AND C.column_id = PFD.minor_id
    -- AND PFD.name = 'Caption'  -- 可依需求過濾說明名稱
LEFT JOIN sys.extended_properties PTB
    ON PTB.class = 1
    AND PTB.minor_id = 0
    AND C.[object_id] = PTB.major_id
-- 索引資訊 CTE
OUTER APPLY (
    SELECT TOP 1
        I.name AS IndexName,
        CASE WHEN I.is_primary_key = 1 THEN N'PK' ELSE N'' END AS PrimaryKey,
        CASE
            WHEN IC.is_descending_key = 1 THEN N'DESC'
            ELSE N'ASC'
        END AS Sort
    FROM sys.indexes I
    INNER JOIN sys.index_columns IC
        ON I.[object_id] = IC.[object_id] AND I.index_id = IC.index_id
    WHERE I.[object_id] = C.[object_id]
        AND IC.column_id = C.column_id
        AND I.type IN (1,2) -- 叢集/非叢集
) IDX
ORDER BY O.name, C.column_id;

關鍵點說明

• OUTER APPLY 取得欄位參與的第一個索引 (PK 優先)，避免多重索引導致列數膨脹。
• extended_properties 支援多重說明，可依 name 過濾 (如 'Caption', 'MS_Description')。
• 適用於 SQL Server 2005 及以上版本 (含 Azure SQL Database)。
• 若需完整索引欄位清單，可另行查詢 sys.index_columns 彙總。

擴充應用

• 產生資料字典文件 (匯出 Excel/Word)。
• 比對開發/測試/生產環境結構差異。
• 自動化建立/更新資料表說明 (搭配 sp_addextendedproperty)。