SQL Server 獲取資料庫、資料表、欄位名稱的系統目錄查詢速查表

核心系統目錄視圖 (SQL Server 2005+ 推薦)

舊版 sysobjects、syscolumns 已棄用，現代版本請使用標準目錄視圖：

1. 列出所有使用者資料庫

SELECT name, database_id, create_date, state_desc
FROM sys.databases
WHERE database_id > 4  -- 排除 system databases (master, tempdb, model, msdb)
  AND state_desc = 'ONLINE'
ORDER BY name;

2. 列出目前資料庫所有使用者資料表

SELECT 
    t.name AS TableName,
    s.name AS SchemaName,
    t.create_date,
    t.modify_date,
    p.rows AS RowCount
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)
WHERE t.is_ms_shipped = 0
ORDER BY s.name, t.name;

3. 列出指定資料表所有欄位詳細資訊

SELECT 
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable,
    c.is_identity,
    c.default_object_id,
    OBJECT_DEFINITION(c.default_object_id) AS DefaultValue,
    ep.value AS Description
FROM sys.columns c
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN sys.extended_properties ep 
    ON ep.major_id = c.object_id 
   AND ep.minor_id = c.column_id
   AND ep.name = 'MS_Description'
WHERE c.object_id = OBJECT_ID('dbo.YourTableName')
ORDER BY c.column_id;

相容性查詢 (SQL Server 2000 / 7.0 僅支援)

1. 所有資料庫名稱

SELECT name FROM master.dbo.sysdatabases;

2. 所有使用者資料表

SELECT name 
FROM sysobjects 
WHERE xtype = 'U'  -- U = User Table
ORDER BY name;

3. 指定資料表所有欄位名稱

SELECT name 
FROM syscolumns 
WHERE id = OBJECT_ID('YourTableName')
ORDER BY colid;

進階：一次查詢所有表的所有欄位 (含結構描述)

SELECT 
    DB_NAME() AS DatabaseName,
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    ty.name AS DataType,
    CASE 
        WHEN ty.name IN ('varchar','nvarchar','char','nchar') THEN c.max_length / CASE WHEN ty.name LIKE 'n%' THEN 2 ELSE 1 END
        WHEN ty.name IN ('decimal','numeric') THEN c.precision
        ELSE c.max_length
    END AS LengthOrPrecision,
    c.scale,
    c.is_nullable,
    c.is_identity,
    ISNULL(ep.value, '') AS ColumnDescription
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_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
   AND ep.name = 'MS_Description'
WHERE t.is_ms_shipped = 0
ORDER BY s.name, t.name, c.column_id;

實用腳本：產生資料字典 Excel 匯出

-- 直接在 SSMS 結果格設為「網格」→ 右鍵另存為 .csv → Excel 開啟
SELECT 
    'Database' = DB_NAME(),
    'Schema' = s.name,
    'Table' = t.name,
    'Column' = c.name,
    'Type' = ty.name + 
        CASE WHEN ty.name IN ('varchar','nvarchar','char','nchar','varbinary','binary') 
             THEN '(' + CAST(CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / CASE WHEN ty.name LIKE 'n%' THEN 2 ELSE 1 END AS VARCHAR) END) + ')'
             WHEN ty.name IN ('decimal','numeric') 
             THEN '(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')'
             ELSE ''
        END,
    'Nullable' = CASE c.is_nullable WHEN 1 THEN 'YES' ELSE 'NO' END,
    'Identity' = CASE c.is_identity WHEN 1 THEN 'YES' ELSE 'NO' END,
    'Default' = ISNULL(OBJECT_DEFINITION(c.default_object_id), ''),
    'Description' = ISNULL(ep.value, '')
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_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
   AND ep.name = 'MS_Description'
WHERE t.is_ms_shipped = 0
ORDER BY s.name, t.name, c.column_id;

總結對照表

掌握現代目錄視圖查詢，不僅相容未來版本，更能取得豐富的結構描述中繼資料，是資料庫文件化、自動化部署、程式碼產生器的基石。