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_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_INMAININFO

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_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 t
LEFT 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 t
JOIN
    ALL_TAB_COLUMNS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN
    ALL_COL_COMMENTS com ON c.OWNER = com.OWNER 
                          AND c.TABLE_NAME = com.TABLE_NAME 
                          AND c.COLUMN_NAME = com.COLUMN_NAME
WHERE
    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 t
JOIN
    ALL_TAB_COLUMNS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN
    ALL_COL_COMMENTS com ON c.OWNER = com.OWNER 
                          AND c.TABLE_NAME = com.TABLE_NAME 
                          AND c.COLUMN_NAME = com.COLUMN_NAME
WHERE
    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 T


SELECT 
    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_name
    AND a.constraint_type = 'P'
    AND a.table_name = 'FIN_IPR_INMAININFO';