SQL Server 2005+ 查詢表結構、索引與擴充屬性的完整資訊腳本

一鍵查詢腳本（適用 SQL 2005 以上版本）

SELECT
    TableName        = CASE WHEN c.column_id = 1 THEN o.name ELSE N'' END,
    TableDescription = ISNULL(CASE WHEN c.column_id = 1 THEN pTable.[value] END, N''),
    ColumnID         = c.column_id,
    ColumnName       = c.name,
    PrimaryKey       = ISNULL(idxPK.PrimaryKey, N''),
    IsIdentity       = CASE WHEN c.is_identity = 1 THEN N'√' ELSE N'' END,
    IsComputed       = CASE WHEN c.is_computed = 1 THEN N'√' ELSE N'' END,
    DataType         = t.name,
    MaxLength        = c.max_length,
    Precision        = c.precision,
    Scale            = c.scale,
    IsNullable       = CASE WHEN c.is_nullable = 1 THEN N'√' ELSE N'' END,
    DefaultValue     = ISNULL(dc.definition, N''),
    ColumnDescription= ISNULL(pCol.[value], N''),
    IndexName        = ISNULL(idx.IndexName, N''),
    IndexSort        = ISNULL(idx.Sort, N''),
    CreateDate       = o.create_date,
    ModifyDate       = 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 dc
    ON c.object_id = dc.parent_object_id
    AND c.column_id = dc.parent_column_id
    AND c.default_object_id = dc.object_id
LEFT JOIN sys.extended_properties pCol
    ON pCol.class = 1
    AND c.object_id = pCol.major_id
    AND c.column_id = pCol.minor_id
    -- AND pCol.name = 'MS_Description'  -- 標準描述名稱
LEFT JOIN sys.extended_properties pTable
    ON pTable.class = 1
    AND pTable.minor_id = 0
    AND c.object_id = pTable.major_id
    -- AND pTable.name = 'MS_Description'
LEFT JOIN (
    SELECT
        ic.object_id,
        ic.column_id,
        IndexName = i.name,
        Sort = CASE INDEXKEY_PROPERTY(ic.object_id, ic.index_id, ic.index_column_id, 'IsDescending')
                 WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
        PrimaryKey = CASE WHEN i.is_primary_key = 1 THEN 'PK' ELSE '' END
    FROM sys.index_columns ic
    JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE i.is_hypothetical = 0
) idx ON c.object_id = idx.object_id AND c.column_id = idx.column_id
ORDER BY o.name, c.column_id;

輸出欄位說明

• TableName：僅首欄顯示表名，利於匯出 Excel 讀取。
• TableDescription / ColumnDescription：讀取 sp_addextendedproperty 寫入的 MS_Description。
• PrimaryKey：標示 PK 欄位。
• IndexName / IndexSort：該欄位參與的索引名稱與排序方向（ASC/DESC）。
• IsIdentity、IsComputed、DefaultValue：完整呈現欄位屬性。

擴充用法

• 加入 WHERE o.name = 'YourTable' 針對單表。
• 搭配 sys.foreign_key_columns 查詢 FK 關聯。
• 將結果 INSERT INTO 文件表，建立資料字典基線。

SQL 2000 相容版本

SQL 2000 無 sys.columns 等目錄檢視，需改用 syscolumns、sysobjects、systypes、sysproperties 系統表，邏輯相似但語法較繁瑣。