SQL Server 查詢數據庫表信息：獲取表名、列名、行數的系統表查詢指南

核心系統表介紹

每個 SQL Server 數據庫內部都包含系統表，記錄元數據信息。最常用的兩張：

• sysobjects：存儲數據庫中所有對象 (表、視圖、存儲過程、觸發器、約束等) 的基本信息。
• syscolumns：存儲所有列 (字段) 的詳細定義信息。

查詢所有用戶表名稱與 ID

SELECT name, id 
FROM sysobjects 
WHERE xtype = 'U'  -- U = User Table (用戶表)
ORDER BY name;

常用 xtype 代碼對照：

查詢指定表的所有列名

SELECT name 
FROM syscolumns 
WHERE id IN (
    SELECT id FROM sysobjects 
    WHERE name = 'YourTableName' AND xtype = 'U'
);

現代化寫法：使用信息模式視圖 (推薦)

SQL Server 2000+ 推薦使用 ANSI 標準的 INFORMATION_SCHEMA 視圖，不依賴內部系統表結構，跨版本更穩定。

獲取所有表名

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'  -- 僅基礎表，排除視圖
ORDER BY TABLE_NAME;

獲取指定表的列信息

SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH, 
    IS_NULLABLE, 
    COLUMN_DEFAULT,
    ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'YourTableName'
ORDER BY ORDINAL_POSITION;

進階：獲取表的詳細結構信息 (含主鍵、標識、擴展屬性)

SELECT 
    表名       = CASE WHEN a.colorder = 1 THEN d.name ELSE '' END,
    表說明     = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '') ELSE '' END,
    字段序號   = a.colorder,
    字段名     = a.name,
    標識列     = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END,
    主鍵       = CASE WHEN EXISTS (
                        SELECT 1 FROM sysobjects 
                        WHERE xtype = 'PK' 
                          AND name IN (
                              SELECT name FROM sysindexes 
                              WHERE indid IN (
                                  SELECT indid FROM sysindexkeys 
                                  WHERE id = a.id AND colid = a.colid
                              )
                          )
                      ) THEN '√' ELSE '' END,
    類型       = b.name,
    占用字節   = a.length,
    長度       = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
    小數位     = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),
    允許空     = CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END,
    默認值     = ISNULL(e.text, ''),
    字段說明   = ISNULL(g.[value], '')
FROM syscolumns a
LEFT JOIN systypes b ON a.xusertype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id
LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
WHERE a.id = OBJECT_ID('YourTableName')
ORDER BY a.id, a.colorder;

跨數據庫批量查詢：所有庫、所有表、所有列

方法一：系統表 (舊版兼容)

-- 1. 所有數據庫
SELECT name FROM master..sysdatabases ORDER BY name;

-- 2. 指定庫的所有表
USE YourDB;
SELECT name FROM sysobjects WHERE xtype = 'U' ORDER BY name;

-- 3. 指定表的所有列
SELECT name FROM syscolumns WHERE id = OBJECT_ID('YourTableName');

方法二：元數據視圖 (現代標準，推薦)

-- 1. 所有數據庫
SELECT name FROM sys.databases ORDER BY name;

-- 2. 所有表 (含視圖)
SELECT * FROM information_schema.tables;

-- 3. 指定表的所有列
SELECT * FROM information_schema.columns WHERE table_name = 'YourTableName';

實用腳本：生成數據庫文檔 (Markdown 格式)

DECLARE @db SYSNAME = 'YourDB';
USE @db;

SELECT 
    '## ' + t.TABLE_NAME + CHAR(13) + CHAR(10) +
    '| 列名 | 類型 | 長度 | 允許空 | 默認值 | 說明 |' + CHAR(13) + CHAR(10) +
    '|------|------|------|--------|--------|------|' + CHAR(13) + CHAR(10) +
    STRING_AGG(
        '| ' + c.COLUMN_NAME + ' | ' + c.DATA_TYPE 
        + CASE WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
               THEN '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' ELSE '' END
        + ' | ' + ISNULL(CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR), 'N/A')
        + ' | ' + c.IS_NULLABLE 
        + ' | ' + ISNULL(c.COLUMN_DEFAULT, '無')
        + ' | ' + ISNULL(ep.value, '無') + ' |',
        CHAR(13) + CHAR(10)
    ) + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN sys.extended_properties ep 
    ON ep.major_id = OBJECT_ID(t.TABLE_NAME) 
   AND ep.minor_id = c.ORDINAL_POSITION
   AND ep.class = 1
WHERE t.TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_NAME
ORDER BY t.TABLE_NAME;

註：STRING_AGG 需 SQL Server 2017+，舊版可用 FOR XML PATH 替代。