select a.OWNER           as 外键拥有者,
       a.TABLE_NAME      as 外键表,
       c.COLUMN_NAME     as 外键列,
       b.OWNER           as 主键拥有者,
       b.TABLE_NAME      as 主键表,
       d.COLUMN_NAME     as 主键列,
       c.CONSTRAINT_NAME as 外键列,
       d.CONSTRAINT_NAME as 主键名
from USER_CONSTRAINTS a,
     USER_CONSTRAINTS b,
     USER_CONS_COLUMNS c,
     USER_CONS_COLUMNS d
where a.R_CONSTRAINT_NAME = b.CONSTRAINT_NAME
  and a.CONSTRAINT_TYPE = 'R'
  and b.CONSTRAINT_TYPE = 'P'
  and a.R_OWNER = b.OWNER
  and a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
  and b.CONSTRAINT_NAME = d.CONSTRAINT_NAME
  and a.OWNER = c.OWNER
  and a.TABLE_NAME = c.TABLE_NAME
  and b.OWNER = d.OWNER
  and b.TABLE_NAME = d.TABLE_NAME

select a.OWNER as 主键拥有者, a.TABLE_NAME as 主键表, b.COLUMN_NAME as 主键列, b.CONSTRAINT_NAME as 主键名
from USER_CONSTRAINTS a,
     USER_CONS_COLUMNS b
where a.CONSTRAINT_TYPE = 'P'
  and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
  and a.OWNER = b.OWNER
  and a.TABLE_NAME = b.TABLE_NAME;

-- 用于word版本表
SELECT
-- t1.Table_Name AS 表名称,
-- t3.comments AS 表说明,
t2.COMMENTS                                                    as 名称,
t1.Column_Name                                                 AS 代码,
concat(concat(t1.DATA_TYPE, '('), concat(t1.DATA_LENGTH, ')')) as 数据类型,
case
    when (select count(*)
          from user_cons_columns c
          where c.table_name = t1.TABLE_NAME
            and c.column_name = t1.COLUMN_NAME
            and c.constraint_name =
                (select d.constraint_name
                 from user_constraints d
                 where d.table_name = c.table_name
                   and d.constraint_type = 'P')
         ) > 0 then 'TRUE'
    else 'FALSE' end                                           as 是键,
(case when t1.NULLABLE = 'Y' then 'NULL' else 'Not NULL' end)  as 默认值,
t2.Comments                                                    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
WHERE NOT EXISTS(SELECT t4.Object_Name
                 FROM User_objects t4
                 WHERE t4.Object_Type = 'TABLE'
                   AND t4.Temporary = 'Y'
                   AND t4.Object_Name = t1.Table_Name)
  AND t1.TABLE_NAME = 'GEA_SP_PD_JXPLAN'
ORDER BY t1.Table_Name, t1.Column_ID;
 
 -- 基本
 SELECT t1.Table_Name AS 表名称,
 t3.comments AS 表说明,
 t1.Column_Name AS 字段名称,
 t1.Data_Type AS 数据类型,
 t1.Data_Length AS 长度,
 concat(concat(t1.DATA_TYPE,'('),concat(t1.DATA_LENGTH,')')) as 数据类型,
 t1.NullAble AS 是否为空,
 t2.Comments 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
 WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4
 WHERE t4.Object_Type='TABLE'
 AND t4.Temporary='Y'
 AND t4.Object_Name=t1.Table_Name )
 AND t1.TABLE_NAME = 'GEA_SP_PD_DEFECT_CAUSE_REL'
 ORDER BY t1.Table_Name, t1.Column_ID;
 
 select * from COLS;
 -- 提取列名，数据类型和长度
 select COLUMN_ID,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from cols
 where TABLE_NAME = 'GEA_SP_PD_DEFECT_CAUSE_REL';
 -- 提取列名，列备注
 select COLUMN_NAME,COMMENTS from USER_COL_COMMENTS
 where TABLE_NAME = 'GEA_SP_PD_DEFECT_CAUSE_REL';
 -- 记录表名，表类型和表备注
 select TABLE_NAME,COMMENTS from USER_TAB_COMMENTS
     where TABLE_NAME = 'GEA_SP_PD_DEFECT_CAUSE_REL';
 -- 无表说明和字段说明的简单版
 select t.TABLE_NAME 表名 ,t.COLUMN_ID 序号,t.COLUMN_NAME 字段名 ,t.DATA_TYPE 类型 ,t.DATA_LENGTH 长度 ,t.NULLABLE 是否为空
 from user_tab_columns t