无表映射的大型数据库如何查询全表列及外键关联关系？

全库所有表的列信息、表间关联关系都属于数据库自动维护的元数据，不需要依赖历史备份文档，直接查询数据库内置的系统视图/系统表即可导出，完全适配你的库规模（500+表、每表100列）。

分数据库可直接运行的查询语句 #

以下语句覆盖数据库从20年前到当前版本的兼容范围，运行无额外性能压力：

MySQL / MariaDB #

导出全库所有表的列信息（含表名、列名、数据类型、键标记、注释）

SELECT 
  TABLE_SCHEMA AS '所属库名',
  TABLE_NAME AS '表名',
  ORDINAL_POSITION AS '列排序号',
  COLUMN_NAME AS '列名',
  COLUMN_TYPE AS '列数据类型',
  IS_NULLABLE AS '是否允许空值',
  COLUMN_KEY AS '键标记(PRI主键/UNI唯一/MUL外键)',
  COLUMN_COMMENT AS '列注释'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema','sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

导出全库所有表的外键关联映射（含主从表、关联字段对应关系）

SELECT
  K.TABLE_SCHEMA AS '外键所属从库名',
  K.TABLE_NAME AS '外键所属从表名',
  K.COLUMN_NAME AS '从表关联列',
  R.TABLE_SCHEMA AS '主键所属主库名',
  R.TABLE_NAME AS '主键所属主表名',
  R.COLUMN_NAME AS '主表关联列',
  C.CONSTRAINT_NAME AS '外键约束名'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
  ON K.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
  AND K.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
  ON C.UNIQUE_CONSTRAINT_NAME = R.CONSTRAINT_NAME 
  AND C.UNIQUE_CONSTRAINT_SCHEMA = R.TABLE_SCHEMA
WHERE K.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY K.TABLE_SCHEMA, K.TABLE_NAME;

Oracle（全版本兼容） #

导出全库所有表的列信息

SELECT 
  OWNER AS '所属schema名',
  TABLE_NAME AS '表名',
  COLUMN_ID AS '列排序号',
  COLUMN_NAME AS '列名',
  DATA_TYPE || '(' || DATA_LENGTH || ')' AS '列数据类型',
  NULLABLE AS '是否允许空值',
  COMMENTS AS '列注释'
FROM ALL_TAB_COLUMNS C
LEFT JOIN ALL_COL_COMMENTS M
  ON C.OWNER = M.OWNER 
  AND C.TABLE_NAME = M.TABLE_NAME 
  AND C.COLUMN_NAME = M.COLUMN_NAME
WHERE C.OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
ORDER BY OWNER, TABLE_NAME, COLUMN_ID;

导出全库所有表的外键关联映射

SELECT
  A.OWNER AS '外键所属从schema',
  A.TABLE_NAME AS '外键所属从表名',
  A.COLUMN_NAME AS '从表关联列',
  B.OWNER AS '主键所属主schema',
  B.TABLE_NAME AS '主键所属主表名',
  B.COLUMN_NAME AS '主表关联列',
  A.CONSTRAINT_NAME AS '外键约束名'
FROM ALL_CONS_COLUMNS A
INNER JOIN ALL_CONSTRAINTS C
  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
  AND A.OWNER = C.OWNER
INNER JOIN ALL_CONS_COLUMNS B
  ON C.R_OWNER = B.OWNER 
  AND C.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'R'
ORDER BY A.OWNER, A.TABLE_NAME;

SQL Server（全版本兼容） #

导出全库所有表的列信息

SELECT 
  DB_NAME() AS '所属库名',
  t.TABLE_SCHEMA AS 'schema名',
  t.TABLE_NAME AS '表名',
  c.ORDINAL_POSITION AS '列排序号',
  c.COLUMN_NAME AS '列名',
  DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' AS '列数据类型',
  c.IS_NULLABLE AS '是否允许空值',
  ep.value AS '列注释'
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
  ON c.TABLE_NAME = t.TABLE_NAME 
  AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
LEFT JOIN SYS.EXTENDED_PROPERTIES ep
  ON ep.major_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
  AND ep.minor_id = c.ORDINAL_POSITION
  AND ep.name = 'MS_Description'
WHERE t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME, c.ORDINAL_POSITION;

导出全库所有表的外键关联映射

SELECT
  fk_schema.name AS '外键所属从schema',
  fk_tab.name AS '外键所属从表名',
  fk_col.name AS '从表关联列',
  pk_schema.name AS '主键所属主schema',
  pk_tab.name AS '主键所属主表名',
  pk_col.name AS '主表关联列',
  fk.name AS '外键约束名'
FROM SYS.FOREIGN_KEYS fk
INNER JOIN SYS.FOREIGN_KEY_COLUMNS fkc
  ON fk.object_id = fkc.constraint_object_id
INNER JOIN SYS.TABLES fk_tab
  ON fk.parent_object_id = fk_tab.object_id
INNER JOIN SYS.SCHEMAS fk_schema
  ON fk_tab.schema_id = fk_schema.schema_id
INNER JOIN SYS.COLUMNS fk_col
  ON fkc.parent_object_id = fk_col.object_id 
  AND fkc.parent_column_id = fk_col.column_id
INNER JOIN SYS.TABLES pk_tab
  ON fk.referenced_object_id = pk_tab.object_id
INNER JOIN SYS.SCHEMAS pk_schema
  ON pk_tab.schema_id = pk_schema.schema_id
INNER JOIN SYS.COLUMNS pk_col
  ON fkc.referenced_object_id = pk_col.object_id 
  AND fkc.referenced_column_id = pk_col.column_id
ORDER BY fk_schema.name, fk_tab.name;

PostgreSQL（全版本兼容） #

导出全库所有表的列信息

SELECT 
  table_schema AS '所属schema',
  table_name AS '表名',
  ordinal_position AS '列排序号',
  column_name AS '列名',
  data_type || '(' || character_maximum_length || ')' AS '列数据类型',
  is_nullable AS '是否允许空值',
  col_description((table_schema||'.'||table_name)::regclass::oid, ordinal_position) AS '列注释'
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema','pg_catalog')
ORDER BY table_schema, table_name, ordinal_position;

导出全库所有表的外键关联映射

SELECT
  kcu.table_schema AS '外键所属从schema',
  kcu.table_name AS '外键所属从表名',
  kcu.column_name AS '从表关联列',
  ccu.table_schema AS '主键所属主schema',
  ccu.table_name AS '主键所属主表名',
  ccu.column_name AS '主表关联列',
  tc.constraint_name AS '外键约束名'
FROM information_schema.table_constraints tc
INNER JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
INNER JOIN information_schema.constraint_column_usage ccu
  ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY kcu.table_schema, kcu.table_name;

实操提示 #

• 所有查询已内置过滤规则，自动排除数据库自带的系统库/系统schema，仅返回业务表的相关信息，不会掺杂冗余元数据
• 外键关联查询仅返回数据库层面显式创建的物理外键：如果该老库为了写入性能未在物理层声明外键约束，可基于导出的全列信息，通过列名规则（如从表xxx_id列对应主表id列）匹配推导逻辑关联关系
• 导出结果可直接通过常用数据库客户端一键导出为CSV/Excel格式，无需安装第三方工具

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