Oracle 批量导出表注释和主键

–查询分表（目前分了16张表）
–先查询注释为空的表，也就是11表做示例，统计出来以后11批量修改成1
select owner,column_name,comments,table_name
from all_col_comments a
where a.table_name like ‘TB%1’ and a.comments is null;

–批量注释列
SELECT ‘comment on table ‘|| t.table_name||’ is ’ || ‘’’’ ||
t1.COMMENTS ||‘’‘’|| ‘;’
FROM User_Tab_Cols t, User_Col_Comments t1
WHERE t.table_name = t1.table_name
AND t.column_name = t1.column_name(+)
AND t.table_name in (‘表1’,‘表2’,‘表3’);

–批量注释表名
SELECT ‘comment on table ‘|| t.table_name||’ is ’ || ‘’’’ ||
t.COMMENTS ||‘’‘’|| ‘;’
FROM user_tab_comments t
WHERE t.table_name in (‘表1’,‘表2’,‘表3’);

–单独查询主键
select DISTINCT cols.table_name,cols.column_name,cols.position
from all_constraints cons,all_cons_columns cols
where cols.table_name=‘TBAMLBENEFINFO1’
and cons.CONSTRAINT_TYPE=‘P’
AND cons.CONSTRAINT_NAME=COLS.CONSTRAINT_NAME
AND CONS.OWNER = COLS.OWNER ORDER BY COLS.TABLE_NAME,COLS.POSITION;

–导出表名、表名中文、字段名、字段中文、主键
SELECT a.table_name
, c.comments
, a.column_name
, b.comments
, a.data_type
, a.data_length, a.column_id
, case when (listagg(to_char(d.position)) within group(order by d.position)) is null then null else ‘是’ end --修改主键为中文“是”
–listagg(to_char(d.position)) within group(order by d.position) as position --仅展示主键在第几位
FROM user_tab_cols a
LEFT JOIN user_col_comments b
ON a.table_name = b.table_name
AND a.column_name = b.column_name
LEFT JOIN user_tab_comments c
ON c.table_name = b.table_name
LEFT JOIN
(SELECT ucc.table_name
, ucc.column_name
, ucc.position
FROM user_cons_columns ucc
LEFT JOIN user_constraints uc
ON ucc.constraint_name = uc.constraint_name
AND uc.constraint_type = UPPER(‘p’)
) d
ON d.table_name = b.table_name
AND d.column_name = b.column_name
where a.TABLE_NAME like ‘TB%’
group by a.table_name
, c.comments
, a.column_name
, b.comments
, a.data_type
, a.data_length, a.column_id
ORDER BY a.table_name,
a.column_id asc ;