— 查询表名
SELECT table_name FROM dba_tables WHERE owner = ‘所有者’ ORDER BY table_name
— 查询表注释
SELECT T.table_name,U.COMMENTS As table_comment FROM DBA_TABLES T
JOIN USER_TAB_COMMENTS U ON T.TABLE_NAME =U.TABLE_NAME WHERE OWNER= ‘所有者’ ORDER BY T.table_name;
— 查询表结构
SELECT a.table_name As tableName
, c.comments As tableComment
, a.column_name As columnName
, b.comments As comments
, a.data_type As dataType
, a.data_length As dataLength
, a.data_scale As dataScale
, d.column_position As primaryKey
, d.constraint_name As constraintName
, e.data_default as defaultValue
,(case when (e.nullable = ‘N’) then ‘NO’ else ‘YES’ end) as isNotNull
,(case when f.info2 = 1 then ‘auto_increment’ else null end) as extra
FROM all_tab_cols a
LEFT JOIN all_col_comments b ON b.table_name = a.table_name AND b.column_name = a.column_name AND a.owner = b.schema_name
LEFT JOIN all_tab_comments c ON c.table_name = b.table_name AND c.owner = b.owner
LEFT JOIN ( SELECT dcc.table_name,dcc.column_name,dcc.column_position,dcc.index_owner,dc.constraint_name
FROM all_ind_columns dcc
JOIN all_constraints dc ON dcc.index_name = dc.index_name AND dc.constraint_type = UPPER(‘p’) AND dcc.index_owner = dc.owner) d
ON d.table_name = a.table_name AND d.column_name = a.column_name AND d.index_owner = a.owner
LEFT JOIN all_tab_columns e ON a.table_name = e.table_name AND a.owner = e.owner and a.column_name = e.column_name
LEFT JOIN (SELECT f1.owner,f1.object_name As table_name,f0.name,f0.info2 FROM syscolumns f0
INNER JOIN dba_objects f1 ON f1.object_type = ‘TABLE’ AND info2 =1 AND f1.object_id = f0.id
) f ON f.name = a.column_name AND f.table_name = a.table_name AND f.owner = a.owner
WHERE a.owner = UPPER(‘所有者’)
ORDER BY a.table_name ASC, a.column_id ASC;
— 查询索引
SELECT dcc.table_name As tableName
, dcc.index_name As indexName
, dcc.column_name As columnName
, dcc.column_position As columnPosition
, dc.constraint_type As constraintType
, di.index_type As indexType
, dcc.descend
FROM all_ind_columns dcc
LEFT JOIN all_constraints dc ON dcc.index_name = dc.index_name AND dcc.index_owner = dc.owner
LEFT JOIN all_indexes di ON dcc.index_name = di.index_name AND dcc.index_owner = di.owner
where dcc.index_owner = ‘所有者’
AND (dc.constraint_type not in (‘p’,’U’) or dc.constraint_type is null)
order by dcc.table_name,dcc.index_name;
