学步园

公司要求将整个数据库的varchar或者char的默认值清空，为减少时间写了以下存储过程。

 CREATE PROCEDURE `sp_clearchardefaultvalue`()<br /> BEGIN<br /> DECLARE s_tablename VARCHAR(100);</p> <p> DECLARE s_fieldname VARCHAR(100);</p> <p> /*显示表的数据库中的所有表<br /> SELECT table_name FROM information_schema.tables WHERE table_schema='databasename' Order by table_name ;<br /> */</p> <p>#显示所有varchar的字段<br /> DECLARE cur_table_structure CURSOR<br /> FOR<br /> SELECT table_name,column_name<br /> FROM INFORMATION_SCHEMA.COLUMNS<br /> WHERE table_schema = 'databasename' AND (data_type ='varchar' OR data_type ='char') AND column_default IS NOT NULL<br /> ORDER BY table_name;</p> <p> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s_tablename = NULL,s_fieldname=NULL;</p> <p> OPEN cur_table_structure;<br /> FETCH cur_table_structure INTO s_tablename,s_fieldname;<br /> WHILE ( s_tablename IS NOT NULL) DO<br /> SET @MyQuery=CONCAT("alter table `",s_tablename,"` alter column `",s_fieldname,"` drop default");<br /> PREPARE msql FROM @MyQuery;<br /> EXECUTE msql ;#USING @c; </p> <p> FETCH cur_table_structure INTO s_tablename,s_fieldname;<br /> END WHILE;</p> <p> CLOSE cur_table_structure;</p> <p> END$$<br />