表结构查询

SELECTdecode(LAG(b.TABLE_NAME)OVER(ORDERBYb.TABLE_NAME,b.COLUMN_ID),

       A.TABLE_NAME,NULL,A.TABLE_NAME||DECODE(A.COMMENTS,NULL,'','(' ||A.COMMENTS || ')') ) TABLE_NAME,

       B.COLUMN_NAME,

       B.DATA_TYPE ||

       DECODE(B.DATA_TYPE,'VARCHAR2', '(' || B.DATA_LENGTH ||')',

             'NUMBER',DECODE(B.DATA_PRECISION,NULL, '','(' ||

                      B.DATA_PRECISION ||

                       DECODE(B.DATA_SCALE,0, '',','|| B.DATA_SCALE)||')')) DATA_TYPE,

       B.NULLABLE,

       C.COMMENTS COL_COMMENTS,

       f.column_name key_column

  FROM USER_TAB_COMMENTS A,USER_TAB_COLUMNS B, USER_COL_COMMENTS C,

       (SELECTE.table_name,E.constraint_name,e.column_name

  FROM user_constraints d,

       user_cons_columns e

 WHERE d.constraint_name=e.constraint_name

   AND d.status='ENABLED'

   AND D.constraint_type='P') f

 WHERE A.TABLE_NAME = B.TABLE_NAME

  

   AND A.TABLE_NAME = C.TABLE_NAME

   AND B.COLUMN_NAME = C.COLUMN_NAME

  

   AND b.table_name=f.table_name(+)

   AND b.COLUMN_NAME=f.column_name(+)

  

   AND a.table_type='TABLE'

  

  ORDER BY b.TABLE_NAME, b.COLUMN_ID;