oracle数据库获取指定表的列的相关信息

developer 2022年03月12日数据库 179 0

1.很多时候我们需要从数据库中获取指定表的所有列的相关属性，如 name,commens,datatype,datalength,pk等。下面就是制定的语句。

  select c.TABLE_NAME TABLE_CODE, 
           t.comments TABLE_NAME, 
           C.COLUMN_NAME COL_CODE, 
           c2.comments COL_NAME, 
           c.DATA_TYPE COL_TYPE, 
            decode(c.NULLABLE,'N','1','0') NULLABLE, 
           c.DATA_LENGTH, 
           c.DATA_PRECISION, 
           decode(PK.COLUMN_NAME, null, '0', '1') pk 
      from user_tab_columns c 
      left join user_tab_comments t 
        on c.TABLE_NAME = t.TABLE_NAME 
      join user_col_comments c2 
        on c2.table_name = c.TABLE_NAME 
       and c2.column_name = c.COLUMN_NAME 
      LEFT JOIN (select cu.table_name, CU.column_name 
                   from user_cons_columns cu, user_constraints au 
                  where cu.constraint_name = au.constraint_name 
                    and au.constraint_type = 'P' 
                    and au.table_name = '表名') PK 
        ON PK.COLUMN_NAME = C.COLUMN_NAME 
     where c.Table_Name = '表名' 
     order by c.COLUMN_ID asc