快速清空表注释、字段注释

某些工作场景下需要清空数据库表、字段注释。运行下面代码段中的sql语句，将生成所需要的sql脚本。为防止后悔，还增加的备份脚本的生成。

Oracle版本

清空表注释

-- 查询所有表
select * from user_tab_comments where table_type = 'TABLE' order by table_name;

-- 清空表注释
select table_name,comments,
'comment on table '|| table_name ||' is '''|| comments||''';' as 备份,
'comment on table '|| table_name ||' is '''';' as 清空表注释 
from user_tab_comments where table_type = 'TABLE' order by table_name;

清空字段注释

-- 查询所有字段
select * from user_col_comments;

-- 清空字段注释
select table_name,column_name,comments,
'comment on column '|| table_name ||'.'|| column_name || ' is '''|| comments||''';' as 备份,
'comment on column '|| table_name ||'.'|| column_name || ' is '''';' as 清空字段注释 
from user_col_comments;

Mysql版本

清空表注释

-- 查询所有表
select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name'; 

-- 清空表注释
SELECT TABLE_NAME, TABLE_COMMENT,
concat('ALTER TABLE ',TABLE_NAME,' COMMENT = ''',TABLE_COMMENT,''';') as 备份,
'ALTER TABLE table_name COMMENT = '''';' as 清空表注释
 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name' order by table_name;

清空字段注释

-- 查询所有字段
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name';

-- 清空字段注释
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT,
concat('ALTER TABLE ',TABLE_NAME,' MODIFY ', COLUMN_NAME,' COMMENT = ''',COLUMN_COMMENT,''';') as 备份,
concat('ALTER TABLE ',TABLE_NAME,' MODIFY ', COLUMN_NAME,' COMMENT = '''';') as 清空字段注释
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' order by table_name,ordinal_position;