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

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

下面是语句运行结果

posted @ 2016-07-14 10:22  Franson  阅读(709)  评论(0编辑  收藏  举报