SQL Server 系統表腳本查詢：表結構、列詳情、約束、擴展屬性一鍵獲取

利用系統目錄視圖 (sys.*) 可編寫高效、版本穩定的元數據查詢腳本，支援數據字典生成、代碼生成器、架構對比等工具開發。

腳本一：查詢當前庫所有用戶表

SELECT 
    t.object_id AS TableId,
    t.name AS TableName,
    t.type_desc AS TableType,
    ep.value AS TableDescription
FROM sys.tables t
LEFT JOIN sys.extended_properties ep
    ON ep.major_id = t.object_id AND ep.minor_id = 0 AND ep.class = 1
WHERE t.is_ms_shipped = 0
ORDER BY t.name;

腳本二：查詢指定表完整列信息（含類型、長度、空值、預設值、標識、主鍵、外鍵、擴展屬性）

DECLARE @TableName SYSNAME = N'YourTable';  -- 修改此處

SELECT 
    c.column_id AS ColOrder,
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length AS MaxLength,
    c.precision, c.scale,
    c.is_nullable AS IsNullable,
    c.is_identity AS IsIdentity,
    dc.definition AS DefaultConstraint,
    ep.value AS ColumnDescription,
    -- 主鍵判斷
    CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey,
    -- 外鍵判斷
    CASE WHEN fk.constraint_object_id IS NOT NULL THEN 1 ELSE 0 END AS IsForeignKey,
    fk.referenced_table_name AS RefTable,
    fk.referenced_column_name AS RefColumn
FROM sys.columns c
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN sys.default_constraints dc 
    ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
LEFT JOIN sys.extended_properties ep
    ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.class = 1
-- 主鍵
LEFT JOIN (
    SELECT ic.object_id, ic.column_id
    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.is_primary_key = 1
) pk ON pk.object_id = c.object_id AND pk.column_id = c.column_id
-- 外鍵
LEFT JOIN (
    SELECT fkc.parent_object_id, fkc.parent_column_id,
           OBJECT_NAME(fkc.referenced_object_id) AS referenced_table_name,
           COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS referenced_column_name,
           fk.object_id AS constraint_object_id
    FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
) fk ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id
WHERE c.object_id = OBJECT_ID(@TableName)
ORDER BY c.column_id;

腳本三：數據類型長度規則速查

腳本四：單引號/雙引號在動態 SQL 中的處理

-- 字符串字面量：單引號包裹，內部單引號雙寫轉義
DECLARE @Sql NVARCHAR(MAX) = N'SELECT * FROM T WHERE Name = ''O''Brien'';';

-- 標識符（表名/列名）：雙引號 或 [] 包裹 (QUOTENAME 推薦)
SET @Sql = N'SELECT ' + QUOTENAME(N'My Column') + N' FROM ' + QUOTENAME(N'My Table');

最佳實踐建議

1. 優先使用 sys.* 目錄視圖，避免舊版 sysobjects/syscolumns（已棄用、不支援新特性）。
2. 擴展屬性 (MS_Description) 統一作為數據字典來源，配合 sp_addextendedproperty 維護。
3. 動態 SQL 拼接標識符**務必用 QUOTENAME()**，防注入、支援特殊字符。
4. 腳本參數化：用變量/臨時表傳遞表名，避免字符串拼接錯誤。