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 '1'
    else '0' 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 = 'CLEAND_TMS_DEVICE'
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

concat(concat(DATA_TYPE, '('), concat(DATA_LENGTH, ')')) as 数据类型