oracle查询所有表和表结构信息sql

select * from user_tables;

查询当前用户下所有表

select * from user_tab_columns;

查询用户有所表下字段信息

SELECT * FROM USER_COL_COMMENTS;

查询用户所有字段信息的注释

select * from user_cons_columns ;

查询用户字段约束和字段对应关系

select * from user_constraints;

查询用户所有约束信息

oracle 查询全部表和对应表结构sql

SELECT
T .database_name AS database_name,
T .table_name AS table_name,
T .column_name AS column_name,
T .column_type AS column_type,
T .data_length AS data_length,
T .column_comment AS column_comment,
b.constraint_type AS constraint_type
FROM
(
SELECT
UB.tablespace_name AS database_name,
UTC.table_name AS table_name,
UTC.column_name AS column_name,
UTC.data_type AS column_type,
utc.data_length AS data_length,
ucc.comments AS column_comment
FROM
user_tables ub
LEFT JOIN user_tab_columns utc ON ub.table_name = UTC.table_name
LEFT JOIN user_col_comments ucc ON utc.column_name = ucc.column_name
AND utc.table_name = ucc.table_name
) T
LEFT JOIN (
SELECT
UCC.table_name AS table_name,
ucc.column_name AS column_name,
wm_concat (UC.constraint_type) AS constraint_type
FROM
user_cons_columns ucc
LEFT JOIN user_constraints uc ON UCC.constraint_name = UC.constraint_name
GROUP BY
UCC.table_name,
ucc.column_name
) b ON T .table_name = b.TABLE_NAME
AND T .column_name = b.column_name
————————————————
版权声明：本文为CSDN博主「liyanpeng1989」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/liyanpeng1989/article/details/80116030