Oracle 與 SQL Server 獲取表結構、主鍵、外鍵的標準查詢語句對照

Oracle 版 (Data Dictionary Views)

1. 欄位定義

SELECT column_name,
       data_type,
       data_precision,
       data_scale,
       nullable
FROM   user_tab_columns
WHERE  table_name = 'YOUR_TABLE';

2. 主鍵欄位

SELECT cols.column_name
FROM   user_constraints cons,
       user_cons_columns cols
WHERE  cons.constraint_name = cols.constraint_name
AND    cons.constraint_type = 'P'
AND    cols.table_name = 'YOUR_TABLE';

3. 外鍵關聯 (含參照表/欄)

SELECT DISTINCT cols.column_name,
       ref_tab.table_name   AS ref_table,
       ref_col.column_name  AS ref_column
FROM   user_constraints cons,
       user_cons_columns cols,
       (SELECT t2.table_name, t2.column_name, t1.r_constraint_name
        FROM   user_constraints t1,
               user_cons_columns t2
        WHERE  t1.r_constraint_name = t2.constraint_name
        AND    t1.table_name = 'YOUR_TABLE') ref_tab,
       user_cons_columns ref_col
WHERE  cons.constraint_name = cols.constraint_name
AND    cons.r_constraint_name = ref_tab.r_constraint_name
AND    cons.table_name = 'YOUR_TABLE'
AND    ref_col.constraint_name = ref_tab.r_constraint_name
AND    ref_col.table_name = ref_tab.table_name;

SQL Server 版 (System Catalog Views)

1. 欄位定義

SELECT c.name        AS column_name,
       t.name        AS data_type,
       c.precision   AS data_precision,
       c.scale       AS data_scale,
       c.is_nullable AS nullable
FROM   sys.columns c
JOIN   sys.types t ON c.user_type_id = t.user_type_id
WHERE  c.object_id = OBJECT_ID('dbo.YOUR_TABLE')
ORDER  BY c.column_id;

2. 主鍵欄位 (參考 sp_pkeys 邏輯)

SELECT c.name AS column_name
FROM   sys.indexes i
JOIN   sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN   sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE  i.object_id = OBJECT_ID('dbo.YOUR_TABLE')
AND    i.is_primary_key = 1
ORDER  BY ic.key_ordinal;

3. 外鍵關聯

SELECT OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.' + OBJECT_NAME(fk.parent_object_id) AS child_table,
       COL_NAME(fkc.parent_object_id, fkc.parent_column_id)                            AS child_column,
       OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '.' + OBJECT_NAME(fk.referenced_object_id) AS parent_table,
       COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id)                    AS parent_column
FROM   sys.foreign_keys fk
JOIN   sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE  fk.parent_object_id = OBJECT_ID('dbo.YOUR_TABLE')
OR     fk.referenced_object_id = OBJECT_ID('dbo.YOUR_TABLE');

對照總結

實用技巧

• Oracle 大小寫敏感：table_name = UPPER('your_table')。
• SQL Server OBJECT_ID 可加 Schema：OBJECT_ID('schema.table')。
• 需跨 Schema 查詢時，Oracle 用 ALL_*/DBA_*，SQL Server 加 OBJECT_SCHEMA_NAME。
• 將上述查詢封裝為函數/預存程序，傳入表名即可自動化文檔生成。