MySQL清除所有注释的存储过程

CREATE DEFINER = ‘root’@‘%’
PROCEDURE test.sp_clearcomment()
COMMENT ‘清除所有注释’
BEGIN

DECLARE flag INT DEFAULT 0;

DECLARE tmp VARCHAR(1000) DEFAULT ‘’;

DECLARE cur CURSOR FOR SELECT concat(‘alter table ‘,table_schema,’.’,table_name,’ comment ‘’‘,’‘’;') s FROM information_schema. COLUMNS WHERE table_schema = DATABASE() GROUP BY TABLE_NAME;
DECLARE cur1 CURSOR FOR
select concat('alter table ', '',table_name,' ',
'modify column ', column_name,' ‘,
column_type,
ifnull(concat(’ character set ‘, character_set_name), ‘’),
ifnull(concat(’ collate ', collation_name), ‘’),
if(is_nullable = ‘no’, ’ not null ‘, ‘’),
if(column_default is null, ‘’, concat(’ default ‘, column_default)),
’ ‘, IF(extra=‘DEFAULT_GENERATED’,’’,extra), ‘;’)
as column_definition
from information_schema.columns
WHERE table_schema =DATABASE()
and column_comment is NOT NULL;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;

OPEN cur;

WHILE flag != 1 DO

FETCH cur INTO tmp;

IF flag != 1 THEN

	set @V_P_SQL = tmp;   #需要先 赋值
	prepare stmt from @V_P_SQL;   #准备好sql语句
	EXECUTE stmt;      #执行sql

END IF;

END WHILE;

CLOSE cur;

set flag=0;

OPEN cur1;

WHILE flag != 1 DO

FETCH cur1 INTO tmp;

IF flag != 1 THEN

	set @V_P_SQL1 = tmp;  
	prepare stmt1 from @V_P_SQL1;      
	EXECUTE stmt1;      #执行sql

END IF;

END WHILE;

CLOSE cur1;

END