oracle导出数据字典

SELECT

   t1.TABLE_NAME        AS "表名称",

       t2.comments        AS "表中文注释",

       t1.Column_Name                                AS "字段名称",

       t2.Comments                                   AS "字段中文注释",

       t1.DATA_TYPE                                  AS "字段数据类型",

       t1.DATA_LENGTH                                AS "字段长度",

       LONG_TO_CHAR(t1.TABLE_NAME,T1.COLUMN_NAME)        AS "默认值",

       case when  ta.CONSTRAINT_NAME is null then '' else '1' end   AS "主键" ,

       case when  tr.CONSTRAINT_NAME is null then '' else '1' end   AS "外键"

  FROM cols t1

  LEFT JOIN user_col_comments t2

         ON t1.Table_name = t2.Table_name

        AND t1.Column_Name = t2.Column_Name

  LEFT JOIN user_tab_comments t3

         ON t1.Table_name = t3.Table_name

  LEFT JOIN user_objects t4

         ON t1.table_name = t4.OBJECT_NAME

  left join(select cu.* from user_cons_columns cu, user_constraints au where

cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.owner ='XXX') ta

on ta.TABLE_NAME=t1.TABLE_NAME and ta.COLUMN_NAME=t1.Column_Name

   left join(select cu.* from user_cons_columns cu, user_constraints au where

cu.constraint_name = au.constraint_name and au.constraint_type = 'R' and au.owner ='XXX') tr

on tr.TABLE_NAME=t1.TABLE_NAME and tr.COLUMN_NAME=t1.Column_Name

   WHERE t4.Object_Type = 'TABLE'

           AND t4.Temporary = 'N'

  ORDER BY t1.Table_Name, t1.Column_ID;