沙洋网站定制,大学生对校园网站建设的需求是什么,佛山做网站多少钱,潍坊市网站–查询分表#xff08;目前分了16张表#xff09; –先查询注释为空的表#xff0c;也就是11表做示例#xff0c;统计出来以后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 n…–查询分表目前分了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_NAMECOLS.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 ;
