查看表的名称及注释
SELECT
TABLE_NAME,
COMMENTS
FROM
all_tab_comments
WHERE
OWNER = '数据库schema名称'
AND
TABLE_NAME LIKE '%表名%'
ORDER BY TABLE_NAME
查找表的索引
SELECT
T.*,
I.INDEX_TYPE
FROM
USER_IND_COLUMNS T,
USER_INDEXES I
WHERE
T.INDEX_NAME = I.INDEX_NAME
AND T.TABLE_NAME = I.TABLE_NAME
----指定表
AND T.TABLE_NAME = '表名'
查找表的唯一约束
SELECT
DISTINCT
CU.COLUMN_NAME,
AU.TABLE_NAME,
AU.OWNER
FROM
DBA_CONS_COLUMNS CU,
DBA_CONSTRAINTS AU
WHERE
CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME
AND AU.CONSTRAINT_TYPE = 'U'
-----指定用户名
AND AU.OWNER = '数据库名'
-----指定表名
AND AU.TABLE_NAME = '表名'
ORDER BY cu.COLUMN_NAME ASC;
查找表的外键
SELECT
c.OWNER AS "数据库",
c.CONSTRAINT_NAME AS "约束名称",
c.CONSTRAINT_TYPE AS "约束类型",
c.TABLE_NAME AS "表名",
cl.COLUMN_NAME AS "字段名"
FROM
user_constraints c,
user_cons_columns cl
WHERE
c.CONSTRAINT_NAME = cl.CONSTRAINT_NAME
AND c.constraint_type = 'R'
AND c.table_name='表名'
参考
Oracle查看表索引、主键、外键、约束
https://www.cnblogs.com/hushaojun/p/16368886.html
oracle查询表信息(索引,外键,列等)
https://blog.csdn.net/weixin_43728713/article/details/113971688