SQL Server / Oracle / Access 數據字典查詢：表說明、列說明、主外鍵一鍵獲取

SQL Server (T-SQL) 系統表方案

適用 SQL Server 2000/2005/2008/R2/2012/2014/2016/2017/2019。新版本建議改用 sys.extended_properties + sys.tables/columns 視圖，但舊語法仍兼容。

1. 表級說明（MS_Description）

SELECT
    obj.name AS TableName,
    ep.value AS TableDescription
FROM sysobjects obj
INNER JOIN sysproperties ep ON ep.id = obj.id AND ep.smallid = 0
WHERE obj.xtype = 'U'  -- 用戶表
ORDER BY obj.name;

2. 列級說明

SELECT
    obj.name AS TableName,
    col.colid AS ColumnId,
    col.name AS ColumnName,
    ep.value AS ColumnDescription
FROM sysobjects obj
INNER JOIN syscolumns col ON col.id = obj.id
INNER JOIN sysproperties ep ON ep.id = obj.id AND ep.smallid = col.colid
WHERE obj.xtype = 'U'
ORDER BY obj.name, col.colid;

3. 主鍵 / 外鍵 信息（簡化版）

-- 主鍵
SELECT
    tc.TABLE_NAME,
    kcu.COLUMN_NAME,
    kcu.ORDINAL_POSITION,
    tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
  ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY tc.TABLE_NAME, kcu.ORDINAL_POSITION;

-- 外鍵
SELECT
    fk.name AS FK_Name,
    tp.name AS Parent_Table,
    cp.name AS Parent_Column,
    tr.name AS Referenced_Table,
    cr.name AS Referenced_Column
FROM sys.foreign_keys fk
JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND cp.object_id = tp.object_id
JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id
JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND cr.object_id = tr.object_id
ORDER BY tp.name, fk.name;

Oracle (PL/SQL) 數據字典視圖方案

Oracle 核心視圖：USER_TABLES、USER_TAB_COLUMNS、USER_COL_COMMENTS、USER_CONSTRAINTS、USER_CONS_COLUMNS。

1. 表與列註釋

-- 表註釋
SELECT table_name, comments FROM user_tab_comments WHERE table_type = 'TABLE';

-- 列註釋
SELECT table_name, column_name, comments
FROM user_col_comments
WHERE table_name IN (SELECT table_name FROM user_tables)
ORDER BY table_name, column_id;

2. 主鍵 / 唯一 / 外鍵 / 檢查約束

SELECT
    uc.constraint_name,
    uc.constraint_type,  -- P=PK, U=Unique, R=FK, C=Check
    uc.table_name,
    ucc.column_name,
    ucc.position
FROM user_constraints uc
JOIN user_cons_columns ucc ON uc.constraint_name = ucc.constraint_name
WHERE uc.constraint_type IN ('P', 'U', 'R')
ORDER BY uc.table_name, uc.constraint_type, ucc.position;

3. 外鍵詳細關聯

SELECT
    uc.constraint_name AS fk_name,
    uc.table_name AS child_table,
    ucc.column_name AS child_column,
    uc_r.table_name AS parent_table,
    ucc_r.column_name AS parent_column
FROM user_constraints uc
JOIN user_cons_columns ucc ON uc.constraint_name = ucc.constraint_name
JOIN user_constraints uc_r ON uc.r_constraint_name = uc_r.constraint_name
JOIN user_cons_columns ucc_r ON uc_r.constraint_name = ucc_r.constraint_name
WHERE uc.constraint_type = 'R'
ORDER BY uc.table_name, uc.constraint_name;

Microsoft Access (DAO/ADO) 方案

Access 無標準系統表，需通過 DAO 對象模型或 MSysObjects 系統表（需啟用顯示系統對象）。

VBA 代碼示例

Sub PrintTableInfo()
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim fd As DAO.Field
    Set db = CurrentDb
    For Each td In db.TableDefs
        If (td.Attributes And dbSystemObject) = 0 Then
            Debug.Print "Table: " & td.Name & " | Desc: " & td.Properties("Description")
            For Each fd In td.Fields
                Debug.Print "  Column: " & fd.Name & " | Type: " & fd.Type & " | Desc: " & fd.Properties("Description")
            Next
        End If
    Next
End Sub

通用標準：INFORMATION_SCHEMA（SQL-92）

主流數據庫（MySQL、PostgreSQL、SQL Server、Oracle 12c+、MariaDB）均支持：

-- 表信息
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db';

-- 列信息
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT,
       IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_db'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- 鍵約束
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_db'
ORDER BY TABLE_NAME, CONSTRAINT_NAME;

一鍵導出文檔腳本（Python + pandas 示例）

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mssql+pymssql://user:pwd@host/db')
# 表註釋
tbl = pd.read_sql("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db'", engine)
# 列註釋
col = pd.read_sql("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='db'", engine)
# 鍵
key = pd.read_sql("SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='db'", engine)

with pd.ExcelWriter('Data_Dictionary.xlsx') as writer:
    tbl.to_excel(writer, sheet_name='Tables', index=False)
    col.to_excel(writer, sheet_name='Columns', index=False)
    key.to_excel(writer, sheet_name='Keys', index=False)
print("數據字典導出完成")

總結

掌握各平台數據字典查詢，是數據治理、影響分析、自動化文檔生成的基礎技能。建議將上述查詢封裝為統一接口，按需調用。