删除所有表的PROCEDURE的方法及代码

起来看以下删除所有表PROCEDURE思路方法及代码

CREATE PROCEDURE proc_drop_all_tables
begin
declare v_tablename varchar(50);
declare v_constname varchar(50);
declare flag default 0;
--定义个游标,寻找所有外键
declare cur_constra cursor for select table_name,constra_name
from information_schema.table_constras
where constra_type=\'FOREIGN KEY\'
and table_schema=(select database);
--定义个游标,寻找所有表
declare cur_table cursor for select table_name from information_schema.tables
where table_type=\'BASE TABLE\' and table_schema=( select database);
--定义个游标,寻找所有视图
declare cur_view cursor for select table_name from information_schema.tables
where table_type=\'VIEW\' and table_schema=( select database);
declare continue handler for not found
flag=1;
--循环去删除所有外键
open cur_constra;
repeat
fetch cur_constra o v_tablename,v_constname;
flag= 0 then
@v_sql = concat(\'alter table \',v_tablename,\' drop foreign key \',v_constname);
prepare stmt from @v_sql;
execute stmt;
end ;
until flag=1
end repeat;
close cur_constra;
flag=0;
--循环去删除所有视图
open cur_view;
repeat
fetch cur_view o v_tablename;
flag=0 then
@v_sql = concat(\'drop view \',v_tablename);
prepare stmt from @v_sql;
execute stmt;
end ;
until flag=1
end repeat;
flag=0;
close cur_view;
--循环去删除所有表
open cur_table;
repeat
fetch cur_table o v_tablename;
flag = 0 then
@v_sql = concat(\'drop table \',v_tablename);
prepare stmt from @v_sql ;
execute stmt ;
end ;
until flag=1
end repeat;
deallocate prepare stmt;
close cur_table;
end;

延伸阅读