Mysql 数据字典
select a.table_schema as 库名
,a.table_name as 表名
,a.table_comment as 表注释
,b.COLUMN_NAME as 字段名
,b.COLUMN_TYPE as 字段类型
,b.COLUMN_COMMENT as 字段注释
,b.column_key as 字段标识
from ( -- 表名
SELECT table_schema
,table_name
,table_comment
FROM information_schema.tables
WHERE table_schema = 'rtm' #库名
)a left outer join ( -- 表字段
SELECT TABLE_NAME
,table_schema
,COLUMN_NAME
,COLUMN_TYPE
,COLUMN_COMMENT
,column_key
from information_schema.columns
wHERE TABLE_SCHEMA='rtm' #库名
)b on a.table_name = b.TABLE_NAME
and a.table_schema = b.table_schema
;
Oracle 数据字典
select a.OWNER as 用户名
,a.TABLE_NAME as 表名
,a.COMMENTS as 表注释
,b.COLUMN_NAME as 字段名
,b.COMMENTS as 字段注释
,c.NUM_ROWS as 行数量
from ( -- 表名
SELECT OWNER
,TABLE_NAME
,COMMENTS
FROM dba_tab_comments
WHERE OWNER = 'TJWL_LES'
)a
left join ( -- 表字段
SELECT TABLE_NAME
,OWNER
,COLUMN_NAME
,COMMENTS
from all_col_comments
wHERE OWNER='TJWL_LES'
)b on a.TABLE_NAME = b.TABLE_NAME
and a.OWNER = b.OWNER
INNER join
(--行数据量
SELECT OWNER,
TABLE_NAME,
NUM_ROWS
FROM all_tables
WHERE OWNER = 'TJWL_LES'
and NUM_ROWS>100
)c on c.TABLE_NAME=a.TABLE_NAME
;