oracle查询数据结构滤涉及的sql语句

• 背景：去客户数据库查询表数据。了解表结构以及表字段及索引等信息
• oracle数据库

SELECT t.OWNER AS "用户名",t.TABLE_NAME AS "表名",c.COMMENTS AS "表说明"
FROM ALL_TABLES t
LEFT JOIN ALL_TAB_COMMENTS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAMEAND c.TABLE_TYPE = 'TABLE'
WHERE (t.OWNER = UPPER('HIS')  or t.OWNER = UPPER('SXEMR'))AND t.TABLE_NAME NOT LIKE 'BIN$%' --AND （c.COMMENTS like '%住%' or c.COMMENTS like '%转%' ）AND （c.COMMENTS like '%住院%' ）
ORDER BY c.COMMENTS;select * from HIS.FIN_IPR_INMAININFOSELECT t.OWNER AS "用户名",t.TABLE_NAME AS "表名",c.COMMENTS AS "表说明"
FROM ALL_TABLES t
LEFT JOIN ALL_TAB_COMMENTS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAMEAND c.TABLE_TYPE = 'TABLE'
WHERE t.OWNER = UPPER('LIS')  -- 替换为实际用户名AND t.TABLE_NAME NOT LIKE 'BIN$%'  -- 排除回收站中的表and t.TABLE_NAME  like 'LIS_RE%'
ORDER BY c.COMMENTS;SELECT t.OWNER AS "用户名",t.TABLE_NAME AS "表名",c.COMMENTS AS "表说明"
FROM ALL_TABLES t
LEFT JOIN ALL_TAB_COMMENTS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAMEAND c.TABLE_TYPE = 'TABLE'
WHERE t.OWNER = UPPER('SXEMR')  -- 替换为实际用户名AND t.TABLE_NAME NOT LIKE 'BIN$%'  -- 排除回收站中的表-- and c.COMMENTS  like '%诊断%'
ORDER BY c.COMMENTS asc;SELECTt.TABLE_NAME AS "表名",c.COLUMN_NAME AS "字段名",com.COMMENTS AS "字段描述"
FROMALL_TABLES t
JOINALL_TAB_COLUMNS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME
LEFT JOINALL_COL_COMMENTS com ON c.OWNER = com.OWNER AND c.TABLE_NAME = com.TABLE_NAME AND c.COLUMN_NAME = com.COLUMN_NAME
WHEREt.OWNER = 'HIS' AND c.TABLE_NAME = 'FIN_IPR_INMAININFO'
ORDER BYt.TABLE_NAME,c.COLUMN_ID;  select * from ALL_TAB_COLUMNS;SELECTt.TABLE_NAME AS "表名",c.COLUMN_NAME AS "字段名",com.COMMENTS AS "字段描述",c.DATA_TYPE AS "字段类型"
FROMALL_TABLES t
JOINALL_TAB_COLUMNS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME
LEFT JOINALL_COL_COMMENTS com ON c.OWNER = com.OWNER AND c.TABLE_NAME = com.TABLE_NAME AND c.COLUMN_NAME = com.COLUMN_NAME
WHEREt.OWNER = 'HIS'and t.TABLE_NAME = 'FIN_IPR_INMAININFO'
ORDER BYt.TABLE_NAME,c.COLUMN_ID; SELECT T.PATIENT_NO AS 住院号,T.CARD_NO AS 就诊卡号,T.PATIENT_NO AS 住院号 FROM HIS.FIN_IPR_INMAININFO TSELECT a.constraint_name, a.table_name,a.constraint_type,b.column_name, b.position
FROM all_constraints a, all_cons_columns b
WHERE a.constraint_name = b.constraint_nameAND a.constraint_type = 'P'AND a.table_name = 'FIN_IPR_INMAININFO';