SQL Server 2005 查詢資料表結構、索引與擴充屬性的完整 T-SQL 指令碼

完整查詢指令碼

以下 T-SQL 可一次取得使用者資料表的完整結構描述資訊：欄位定義、主鍵、身分識別、計算欄位、資料類型、長度、精度、小數位、允許 NULL、預設值、擴充屬性描述 (MS_Description)、索引名稱與排序、建立/修改日期。

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 = 'MS_Description'  -- 可依需求過濾描述名稱
LEFT JOIN sys.extended_properties PTB
    ON PTB.class = 1
    AND PTB.minor_id = 0
    AND C.[object_id] = PTB.major_id
    -- AND PTB.name = 'MS_Description'
LEFT JOIN (
    SELECT 
        IDXC.[object_id],
        IDXC.column_id,
        Sort = CASE INDEXKEY_PROPERTY(IDXC.[object_id], IDXC.index_id, IDXC.index_column_id, 'IsDescending')
            WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
        PrimaryKey = CASE WHEN IDX.is_primary_key = 1 THEN N'√' ELSE N'' END,
        IndexName = IDX.name
    FROM sys.index_columns IDXC
    INNER JOIN sys.indexes IDX
        ON IDXC.[object_id] = IDX.[object_id]
        AND IDXC.index_id = IDX.index_id
    WHERE IDX.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：僅在資料表第一個欄位顯示名稱，其餘列留空，利於報表輸出。
• TableDesc / ColumnDesc：取自擴充屬性 MS_Description，需先使用 sp_addextendedproperty 設定。
• PrimaryKey / IndexName / IndexSort：彙整索引資訊，包含主鍵標記與排序方向。
• Create_Date / Modify_Date：資料表建立與最後修改時間。

進階用法

• 加入 WHERE O.name = 'YourTable' 過濾特定資料表。
• 結合 sys.foreign_keys、sys.foreign_key_columns 可延伸查詢外來鍵關聯。
• 結果可直接匯出至 Excel 或報表工具產生資料字典文件。