MySQL与Oracle元数据抽取实战：常用SQL查询指南

-- 数据库
SELECT `schema_name` AS '库名',
       `DEFAULT_CHARACTER_SET_NAME` AS '默认字符集' 
FROM `SCHEMATA`;

-- 表
SELECT `TABLE_NAME` AS '表名',
       `TABLE_COMMENT` AS '描述',
       `TABLE_TYPE` AS '表类型' 
FROM `TABLES`;

-- 字段
SELECT `TABLE_SCHEMA` AS '库名',
       `TABLE_NAME` AS '表名',
       `COLUMN_NAME` AS '字段名',
       `COLUMN_COMMENT` AS '字段描述',
       `DATA_TYPE` AS '字段类型',
       `CHARACTER_MAXIMUM_LENGTH` AS '长度',
       `IS_NULLABLE` AS '是否为空' 
FROM `COLUMNS`;

-- 分区
SELECT `TABLE_SCHEMA` AS '库名',
       `TABLE_NAME` AS '表名',
       `PARTITION_NAME` AS '分区名',
       `PARTITION_ORDINAL_POSITION` AS '分区编号',
       `PARTITION_EXPRESSION` AS '分区函数表达式' 
FROM `PARTITIONS`
WHERE partition_name IS NOT NULL;

-- 索引
SELECT * FROM STATISTICS;

-- 主键
SELECT * FROM `COLUMNS` WHERE COLUMN_KEY = 'PRI';

-- 模式/表空间
SELECT * FROM DBA_TABLESPACES;

-- 表
select ut.TABLE_NAME,
       utc.TABLE_COMMENT as DESCRIPTION
from user_tables ut 
LEFT JOIN user_tab_comments utc ON ut.TABLE_NAME = utc.TABLE_NAME;

-- 字段
select t.TABLE_NAME as 表名,
       t.COLUMN_NAME as 字段名,
       t.DATA_TYPE as 类型,
       t.DATA_LENGTH as 长度,
       t.NULLABLE as 是否为空,
       t.DATA_DEFAULT as 默认值,
       s.comments as 描述
from user_tab_columns t 
LEFT JOIN user_col_comments s ON t.COLUMN_NAME = s.COLUMN_NAME 
WHERE t.TABLE_NAME = 'ASSET_BASE_TABLE';

-- 分区
SELECT * FROM USER_PART_KEY_COLUMNS;

-- 索引
SELECT * FROM USER_INDEXES;

-- 视图
SELECT * FROM USER_VIEWS;

-- 主键
-- 需关联 USER_CONS_COLUMNS 和 USER_CONSTRAINTS 找出类型为 'P' 的主键约束
select cu.* 
from USER_CONS_COLUMNS cu, USER_CONSTRAINTS au 
where cu.constraint_name = au.constraint_name 
and au.constraint_type = 'P';