Bases de datos

MySQL

Generación de script de eliminación de tablas, vistas, funciones y procedimientos

Última actualización: 03-06-2018 16:15

La unión de estos dos SELECTs da como resultado el listado de comandos de eliminación de tablas, vistas, funciones y procedimientos de la base de datos en que se ejecute.

select XSQL
from (
    select 10 as N, concat('drop ', lower(ROUTINE_TYPE), ' ', ROUTINE_NAME, ';')   as XSQL 
    from information_schema.ROUTINES where ROUTINE_SCHEMA = database()
 
    union
    select 20, concat('drop view ', TABLE_NAME, ';') 
    from information_schema.tables 
    where table_schema = database() and TABLE_TYPE = 'VIEW'
 
    union
    select 30, concat('alter table ', TABLE_NAME, ' drop foreign key ', CONSTRAINT_NAME, ';')
    from information_schema.TABLE_CONSTRAINTS
    where TABLE_SCHEMA = database() and CONSTRAINT_TYPE = 'FOREIGN KEY'
 
    union
    select 40, concat('drop table ', TABLE_NAME, ';') 
    from information_schema.tables 
    where table_schema = database() and TABLE_TYPE = 'BASE TABLE'
 
) x 
order by N;