如何在SQL Server和Oracle中批量获取表数据类型、PK/FK约束并导出CSV

SQL Server 环境 #

基于你已知的INFORMATION_SCHEMA.COLUMNS视图，关联约束相关系统视图即可一次性查询所有表的列、数据类型、主键、外键信息，完整查询语句如下：

SELECT 
    c.TABLE_CATALOG,
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    c.DATA_TYPE,
    CASE WHEN tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN '是' ELSE '否' END AS IS_PRIMARY_KEY,
    CASE WHEN tc.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN '是' ELSE '否' END AS IS_FOREIGN_KEY,
    ISNULL(tc.CONSTRAINT_NAME, '') AS CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON c.TABLE_SCHEMA = kcu.TABLE_SCHEMA
    AND c.TABLE_NAME = kcu.TABLE_NAME
    AND c.COLUMN_NAME = kcu.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
    AND kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
    AND tc.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION

导出CSV方法：在SSMS中执行查询后，右键结果集选择「将结果另存为」，选择CSV格式保存即可。

Oracle 环境 #

无需逐表执行describe命令，直接查询系统视图即可获取全库表元数据，完整查询语句如下：

SELECT 
    atc.OWNER AS TABLE_SCHEMA,
    atc.TABLE_NAME,
    atc.COLUMN_NAME,
    atc.DATA_TYPE,
    CASE WHEN ac.CONSTRAINT_TYPE = 'P' THEN '是' ELSE '否' END AS IS_PRIMARY_KEY,
    CASE WHEN ac.CONSTRAINT_TYPE = 'R' THEN '是' ELSE '否' END AS IS_FOREIGN_KEY,
    NVL(ac.CONSTRAINT_NAME, '') AS CONSTRAINT_NAME
FROM ALL_TAB_COLUMNS atc
LEFT JOIN ALL_CONS_COLUMNS acc
    ON atc.OWNER = acc.OWNER
    AND atc.TABLE_NAME = acc.TABLE_NAME
    AND atc.COLUMN_NAME = acc.COLUMN_NAME
LEFT JOIN ALL_CONSTRAINTS ac
    ON acc.OWNER = ac.OWNER
    AND acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME
    AND ac.CONSTRAINT_TYPE IN ('P', 'R')
-- 若仅需查询当前用户名下的表，取消下方注释即可
-- WHERE atc.OWNER = USER
ORDER BY atc.OWNER, atc.TABLE_NAME, atc.COLUMN_ID

导出CSV方法：在SQL*Plus中执行以下命令即可直接导出结果为CSV：

set colsep ,
set headsep off
set pagesize 0
set trimspool on
spool /your/path/table_meta.csv
-- 执行上述查询语句
spool off

也可在PL/SQL Developer等可视化工具中执行查询后，右键结果集直接导出为CSV格式。

内容的提问来源于stack exchange，提问作者ANIMESH GUPTA