SQL Server 取得所有資料庫、資料表、欄位名稱的系統表查詢指南

SQL Server 系統目錄查詢速查

1. 取得所有使用者資料庫名稱

SELECT name 
FROM master..sysdatabases 
WHERE dbid > 4  -- 排除 master, tempdb, model, msdb
ORDER BY name;

2. 取得指定資料庫所有使用者資料表名稱

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

xtype 常用值：

• U：使用者資料表
• S：系統資料表
• V：檢視表
• P：預存程序
• FN：純量函數
• TF：資料表值函數

3. 取得指定資料表所有欄位資訊

USE YourDatabase;
GO
SELECT 
    c.name AS ColumnName,
    t.name AS DataType,
    c.length AS MaxLength,
    c.prec AS Precision,
    c.scale AS Scale,
    c.isnullable AS IsNullable,
    c.colorder AS ColumnOrder,
    CASE WHEN c.status & 0x80 = 0x80 THEN 1 ELSE 0 END AS IsIdentity,
    COMMENTS.text AS DefaultValue
FROM syscolumns c
INNER JOIN systypes t ON c.xtype = t.xtype
LEFT JOIN syscomments COMMENTS ON c.cdefault = COMMENTS.id
WHERE c.id = OBJECT_ID('YourTableName')
ORDER BY c.colorder;

現代版本（SQL 2005+）推薦寫法：INFORMATION_SCHEMA

符合 ANSI 標準、跨版本相容、欄位語意更清晰：

資料庫列表

SELECT CATALOG_NAME AS DatabaseName 
FROM INFORMATION_SCHEMA.SCHEMATA;

資料表列表

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE';

欄位詳細資訊

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

Access (Jet/ACE) 資料庫對應查詢

-- 取得所有使用者資料表
SELECT name 
FROM MSysObjects 
WHERE type = 1 AND flags = 0;  -- type=1 表, flags=0 非系統

-- 判斷特定表是否存在
SELECT * 
FROM MSysObjects 
WHERE name = 'ExamResultTime' AND type = 1 AND flags = 0;

注意：MSysObjects 預設隱藏，需在 Access 選項中勾選「顯示系統物件」才能在介面看見，但 SQL 查詢不受影響。

進階：取得完整結構描述腳本（SQL 2005+）

-- 產生建表腳本（需 SSMS 或 SMO）
-- SSMS：右鍵資料庫 → 任務 → 產生指令碼
-- T-SQL 無法直接輸出完整 DDL，建議使用 PowerShell + SMO 或第三方工具

權限需求

• 查詢 master..sysdatabases：需 VIEW ANY DATABASE 或 sysadmin
• 查詢使用者資料庫系統表：需該資料庫 public 角色即可（預設可見）
• INFORMATION_SCHEMA：僅回傳使用者有權限的物件，安全性更好