CREATE PROCEDURE proc_drop_all_tables()
begin
declare v_tablename varchar(50);
declare v_constname varchar(50);
declare flag int default 0;
--定义一个游标,寻找所有的外键
declare cur_constraint cursor for
select table_name,constraint_name
from information_schema.table_constraints
where constraint_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
set flag=1;
--循环去删除所有外键
open cur_constraint;
repeat
fetch cur_constraint into v_tablename,v_constname;
if flag= 0 then
set @v_sql = concat('alter table ',v_tablename,'
drop foreign key ',v_constname);
prepare stmt from @v_sql;
execute stmt;
end if;
until flag=1
end repeat;
close cur_constraint;
set flag=0;
--循环去删除所有视图
open cur_view;
repeat
fetch cur_view into v_tablename;
if flag=0 then
set @v_sql = concat('drop view ',v_tablename);
prepare stmt from @v_sql;
execute stmt;
end if;
until flag=1
end repeat;
set flag=0;
close cur_view;
--循环去删除所有表
open cur_table;
repeat
fetch cur_table into v_tablename;
if flag = 0 then
set @v_sql = concat('drop table ',v_tablename);
prepare stmt from @v_sql ;
execute stmt ;
end if;
until flag=1
end repeat;
deallocate prepare stmt;
close cur_table;
end;
