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

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

SELECT t.OWNER AS \"用户名\", t.TABLE_NAME AS \"表名\", c.COMMENTS AS \"表说明\"FROM ALL_TABLES tLEFT JOIN ALL_TAB_COMMENTS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME AND 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 tLEFT JOIN ALL_TAB_COMMENTS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME AND 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 tLEFT JOIN ALL_TAB_COMMENTS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME AND 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;SELECT t.TABLE_NAME AS \"表名\", c.COLUMN_NAME AS \"字段名\", com.COMMENTS AS \"字段描述\"FROM ALL_TABLES tJOIN ALL_TAB_COLUMNS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAMELEFT JOIN ALL_COL_COMMENTS com ON c.OWNER = com.OWNER AND c.TABLE_NAME = com.TABLE_NAME AND c.COLUMN_NAME = com.COLUMN_NAMEWHERE t.OWNER = \'HIS\' AND c.TABLE_NAME = \'FIN_IPR_INMAININFO\'ORDER BY t.TABLE_NAME, c.COLUMN_ID; select * from ALL_TAB_COLUMNS;SELECT t.TABLE_NAME AS \"表名\", c.COLUMN_NAME AS \"字段名\", com.COMMENTS AS \"字段描述\", c.DATA_TYPE AS \"字段类型\"FROM ALL_TABLES tJOIN ALL_TAB_COLUMNS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAMELEFT JOIN ALL_COL_COMMENTS com ON c.OWNER = com.OWNER AND c.TABLE_NAME = com.TABLE_NAME AND c.COLUMN_NAME = com.COLUMN_NAMEWHERE t.OWNER = \'HIS\' and t.TABLE_NAME = \'FIN_IPR_INMAININFO\'ORDER BY t.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.positionFROM all_constraints a, all_cons_columns bWHERE a.constraint_name = b.constraint_name AND a.constraint_type = \'P\' AND a.table_name = \'FIN_IPR_INMAININFO\';