【Oracle】通过 sql 查看表结构

oracle 通过 sql 查看表结构

作者:Moonce
博客:望舒阁

SELECT
 case T3.CONSTRAINT_TYPE
         when 'P' then 'true'
  else 'false' end as PK,
       T1.COLUMN_NAME as Name,
       T1.DATA_TYPE  as Type,
       T1.DATA_LENGTH as Len,
       case T4.CONSTRAINT_TYPE
         when 'C' then 'true'
  else 'false' end as Not_Null,
       case T5.CONSTRAINT_TYPE
         when 'P' then 'true'
  when 'U' then 'true'
  else 'false' end as Unique_Column,
       T6.DATA_DEFAULT as DEFAULT_Column,
       T2.COMMENTS as Notes
FROM USER_TAB_COLS T1
left join USER_COL_COMMENTS T2
    on T1.TABLE_NAME = T2.TABLE_NAME
         AND T1.COLUMN_NAME = T2.COLUMN_NAME
left join (select au.* ,cu.COLUMN_NAME from user_cons_columns cu, user_constraints au
           where cu.constraint_name = au.constraint_name and au.CONSTRAINT_TYPE = 'P')  T3
    on T1.TABLE_NAME = T3.TABLE_NAME
    AND T1.COLUMN_NAME = T3.COLUMN_NAME
left join (select au.* ,cu.COLUMN_NAME from user_cons_columns cu, user_constraints au
           where cu.constraint_name = au.constraint_name and au.CONSTRAINT_TYPE = 'C' ) T4
    on T1.TABLE_NAME = T4.TABLE_NAME
    AND T1.COLUMN_NAME = T4.COLUMN_NAME
left join (select au.* ,cu.COLUMN_NAME from user_cons_columns cu, user_constraints au
           where cu.constraint_name = au.constraint_name and (au.CONSTRAINT_TYPE = 'P'or au.CONSTRAINT_TYPE = 'U')) T5
    on T1.TABLE_NAME = T5.TABLE_NAME
    AND T1.COLUMN_NAME = T5.COLUMN_NAME
left join USER_TAB_COLUMNS T6
    on T1.TABLE_NAME = T6.TABLE_NAME
    AND T1.COLUMN_NAME = T6.COLUMN_NAME
WHERE
  T1.TABLE_NAME =upper('T_CASES')
and T1.COLUMN_NAME = T2.COLUMN_NAME
order by T3.CONSTRAINT_TYPE asc;

结果样式如下