empty - MySQL DROP todas las tablas, ignorando claves externas

mysql workbench drop all tables

Question

¿Existe una forma fácil y sencilla de eliminar todas las tablas de una base de datos MySQL, ignorando cualquier restricción de clave externa que pueda haber allí?

Answer 1

Aquí hay una forma automatizada de hacerlo a través de un script bash:

host=$1
dbName=$2
user=$3
password=$4

if [ -z "$1" ]
then
    host="localhost"
fi

# drop all the tables in the database
for i in `mysql -u$user -p$password $dbName -e "show tables" | grep -v Tables_in` ; do  echo $i && mysql -u$user -p$password $dbName -e "SET FOREIGN_KEY_CHECKS = 0; drop table $i ; SET FOREIGN_KEY_CHECKS = 1" ; done

Answer 2

Aquí hay una solución basada en el cursor. Un poco largo, pero funciona como un único lote de SQL:

DROP PROCEDURE IF EXISTS `drop_all_tables`;

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

    OPEN _cursor;

    REPEAT FETCH _cursor INTO _tableName;

    IF NOT _done THEN
        SET @stmt_sql = CONCAT(''DROP TABLE '', _tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    UNTIL _done END REPEAT;

    CLOSE _cursor;

END$$

DELIMITER ;

call drop_all_tables(); 

DROP PROCEDURE IF EXISTS `drop_all_tables`;

Answer 3

Aquí se modifica el procedimiento almacenado de SurlyDre para que se ignoren las claves externas:

DROP PROCEDURE IF EXISTS `drop_all_tables`;

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

    SET FOREIGN_KEY_CHECKS = 0;

    OPEN _cursor;

    REPEAT FETCH _cursor INTO _tableName;

    IF NOT _done THEN
        SET @stmt_sql = CONCAT(''DROP TABLE '', _tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    UNTIL _done END REPEAT;

    CLOSE _cursor;
    SET FOREIGN_KEY_CHECKS = 1;
END$$

DELIMITER ;

call drop_all_tables(); 

DROP PROCEDURE IF EXISTS `drop_all_tables`;

Answer 4

Buscar en Google en el tema siempre me lleva a esta pregunta SO, de modo que aquí está funcionando el código mysql que elimina AMBAS tablas y vistas:

DROP PROCEDURE IF EXISTS `drop_all_tables`;

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

    SET FOREIGN_KEY_CHECKS = 0;

    OPEN _cursor;

    REPEAT FETCH _cursor INTO _tableName;

    IF NOT _done THEN
        SET @stmt_sql1 = CONCAT(''DROP TABLE IF EXISTS '', _tableName);
        SET @stmt_sql2 = CONCAT(''DROP VIEW IF EXISTS '', _tableName);

        PREPARE stmt1 FROM @stmt_sql1;
        PREPARE stmt2 FROM @stmt_sql2;

        EXECUTE stmt1;
        EXECUTE stmt2;

        DEALLOCATE PREPARE stmt1;
        DEALLOCATE PREPARE stmt2;
    END IF;

    UNTIL _done END REPEAT;

    CLOSE _cursor;
    SET FOREIGN_KEY_CHECKS = 1;
END$$

DELIMITER ;

call drop_all_tables();

DROP PROCEDURE IF EXISTS `drop_all_tables`;

Answer 5

De http://www.devdaily.com/blog/post/mysql/drop-mysql-tables-in-any-order-foreign-keys :

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists customers;
drop table if exists orders;
drop table if exists order_details;
SET FOREIGN_KEY_CHECKS = 1;

(Tenga en cuenta que esto responde cómo deshabilitar las comprobaciones de claves foráneas para poder eliminar las tablas en un orden arbitrario. No responde cómo generar automáticamente declaraciones sin tablas para todas las tablas existentes y ejecutarlas en un solo script. Respuesta de Jean hace.)

Answer 6

De esta respuesta ,

ejecutar:

  use `dbName`; --your db name here
  SET FOREIGN_KEY_CHECKS = 0; 
  SET @tables = NULL;
  SET GROUP_CONCAT_MAX_LEN=32768;

  SELECT GROUP_CONCAT(''`'', table_schema, ''`.`'', table_name, ''`'') INTO @tables
  FROM   information_schema.tables 
  WHERE  table_schema = (SELECT DATABASE());
  SELECT IFNULL(@tables, '''') INTO @tables;

  SET        @tables = CONCAT(''DROP TABLE IF EXISTS '', @tables);
  PREPARE    stmt FROM @tables;
  EXECUTE    stmt;
  DEALLOCATE PREPARE stmt;
  SET        FOREIGN_KEY_CHECKS = 1;

Esto elimina las tablas de la base de datos actualmente en uso. Puede configurar la base de datos actual utilizando.

O de lo contrario, la respuesta aceptada de Dion es más simple, excepto que necesita ejecutarla dos veces, primero para obtener la consulta y, en segundo lugar, para ejecutar la consulta. Proporcioné algunas tontas tachuelas para escapar de caracteres especiales en nombres de tablas y db.

  SELECT CONCAT(''DROP TABLE IF EXISTS `'', table_schema, ''`.`'', table_name, ''`;'')
  FROM   information_schema.tables
  WHERE  table_schema = ''dbName''; --your db name here

Answer 7

En PHP es tan fácil como:

$pdo = new PDO(''mysql:dbname=YOURDB'', ''root'', ''root'');

$pdo->exec(''SET FOREIGN_KEY_CHECKS = 0'');

$query = "SELECT concat(''DROP TABLE IF EXISTS '', table_name, '';'')
          FROM information_schema.tables
          WHERE table_schema = ''YOURDB''";

foreach($pdo->query($query) as $row) {
    $pdo->exec($row[0]);
}

$pdo->exec(''SET FOREIGN_KEY_CHECKS = 1'');

Solo recuerde cambiar YOURDB al nombre de su base de datos, y obviamente el usuario / pase.

Answer 8

En un shell de Linux como bash / zsh:

DATABASE_TO_EMPTY="your_db_name";
{ echo "SET FOREIGN_KEY_CHECKS = 0;" ; /
  mysql "$DATABASE_TO_EMPTY" --skip-column-names -e /
  "SELECT concat(''DROP TABLE IF EXISTS '', table_name, '';'') /
   FROM information_schema.tables WHERE table_schema = ''$DATABASE_TO_EMPTY'';";/
  } | mysql "$DATABASE_TO_EMPTY"

Esto generará los comandos, luego los canalizará inmediatamente a una segunda instancia de cliente que eliminará las tablas.

Por supuesto, la parte inteligente está copiada de otras respuestas aquí. Solo quería una frase de una sola línea (ish) para hacer el trabajo que el OP quería.

Tenga en cuenta que, por supuesto, también tendrá que poner sus credenciales (dos veces) en estos comandos mysql, a menos que tenga una configuración de seguridad muy baja. (o podría alias su comando mysql para incluir sus credenciales).

Answer 9

Encontré útil el conjunto generado de declaraciones de descarte, y recomiendo estos ajustes:

1. Limite las gotas generadas a su base de datos de esta manera:

SELECT concat(''DROP TABLE IF EXISTS `'', table_name, ''`;'')
FROM information_schema.tables
WHERE table_schema = ''MyDatabaseName'';

Nota: Esto no ejecuta las declaraciones DROP, solo le da una lista de ellas. Necesitará cortar y pegar la salida en su motor SQL para ejecutarlos.

2. Tenga en cuenta, según http://dev.mysql.com/doc/refman/5.5/en/drop-table.html , dejar caer en cascada es inútil / engañoso:

"A RESTRICT y CASCADE se les permite facilitar la portabilidad. En MySQL 5.5, no hacen nada".

Por lo tanto, para que las instrucciones de descarte funcionen si necesita:

SET FOREIGN_KEY_CHECKS = 0

Esto deshabilitará las verificaciones de integridad referencial, por lo que cuando haya terminado de realizar las caídas que necesita, deberá restablecer la verificación de claves con

SET FOREIGN_KEY_CHECKS = 1

3. La ejecución final debería verse como:

SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;

NB: para usar la salida de SELECT más fácil, la opción mysql -B puede ayudar.

Answer 10

Esta es una publicación bastante antigua, pero ninguna de las respuestas aquí realmente respondió la pregunta en mi opinión, ¡así que espero que mi publicación ayude a la gente!

Encontré esta solución en otra pregunta que me funciona muy bien:

mysql -Nse ''show tables'' DB_NAME | while read table; do mysql -e "SET FOREIGN_KEY_CHECKS=0; truncate table /`$table/`" DB_NAME; done

Eso realmente DB_NAME todas sus tablas en la base de datos DB_NAME , y no solo mostrará la línea de comando TRUNCATE .

¡Espero que esto ayude!

Answer 11

Esta solución se basa en la respuesta de @SkyLeach pero con el soporte de eliminar tablas con claves externas.

echo "SET FOREIGN_KEY_CHECKS = 0;" > ./drop_all_tables.sql
mysqldump --add-drop-table --no-data -u user -p dbname | grep ''DROP TABLE'' >> ./drop_all_tables.sql
echo "SET FOREIGN_KEY_CHECKS = 1;" >> ./drop_all_tables.sql
mysql -u user -p dbname < ./drop_all_tables.sql

Answer 12

Se me ocurrió esta modificación en la respuesta de Dion Truter para hacerlo más fácil con muchas tablas:

SET GROUP_CONCAT_MAX_LEN = 10000000;
SELECT CONCAT(''SET FOREIGN_KEY_CHECKS=0;/n'', 
              GROUP_CONCAT(CONCAT(''DROP TABLE IF EXISTS `'', table_name, ''`'')
                           SEPARATOR '';/n''),
              '';/nSET FOREIGN_KEY_CHECKS=1;'')
FROM information_schema.tables
WHERE table_schema = ''SchemaName'';

Esto devuelve todo en un campo, por lo que puede copiar una vez y eliminar todas las tablas (use Copy Field Content (unquoted) en Workbench). Si tiene GROUP_CONCAT() tablas, puede llegar a algunos límites en GROUP_CONCAT() . Si es así, aumente la variable max len (y max_allowed_packet , si es necesario).

Answer 13

Si en Linux (o cualquier otro sistema que admita tuberías, eco y grep) puede hacerlo con una línea:

echo "SET FOREIGN_KEY_CHECKS = 0;" > temp.txt; /
mysqldump -u[USER] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP >> temp.txt; /
echo "SET FOREIGN_KEY_CHECKS = 1;" >> temp.txt; /
mysql -u[USER] -p[PASSWORD] [DATABASE] < temp.txt;

Sé que esta es una vieja pregunta, pero creo que este método es rápido y simple.

Answer 14

Simplemente ponga aquí un comentario útil hecho por Jonathan Watt para eliminar todas las tablas

MYSQL="mysql -h HOST -u USERNAME -pPASSWORD DB_NAME"
$MYSQL -BNe "show tables" | awk ''{print "set foreign_key_checks=0; drop table `" $1 "`;"}'' | $MYSQL
unset MYSQL

Me ayuda y espero que sea de utilidad.

Answer 15

Sobre la base de la respuesta de @Dion Truter y @Wade Williams, el siguiente script de shell eliminará todas las tablas, después de mostrar primero lo que está a punto de ejecutarse, y le dará la oportunidad de abortar usando Ctrl-C.

#!/bin/bash

DB_HOST=xxx
DB_USERNAME=xxx
DB_PASSWORD=xxx
DB_NAME=xxx

CMD="mysql -sN -h ${DB_HOST} -u ${DB_USERNAME} -p${DB_PASSWORD} ${DB_NAME}"

# Generate the drop statements
TMPFILE=/tmp/drop-${RANDOM}.sql
echo ''SET FOREIGN_KEY_CHECKS = 0;'' > ${TMPFILE}
${CMD} $@ >> ${TMPFILE} << ENDD
    SELECT concat(''DROP TABLE IF EXISTS /`'', table_name, ''/`;'')
    FROM information_schema.tables
    WHERE table_schema = ''${DB_NAME}'';
ENDD
echo ''SET FOREIGN_KEY_CHECKS = 1;'' >> ${TMPFILE}

# Warn what we are about to do
echo
cat ${TMPFILE}
echo
echo "Press ENTER to proceed (or Ctrl-C to abort)."
read

# Run the SQL
echo "Dropping tables..."
${CMD} $@ < ${TMPFILE}
echo "Exit status is ${?}."
rm ${TMPFILE}

Answer 16

Todos los enfoques anteriores incluyen mucho más trabajo que este AFAICT ...

( mysqldump --add-drop-table --no-data -u root -p database | grep ''DROP TABLE'' ) > ./drop_all_tables.sql
mysql -u root -p database < ./drop_all_tables.sql

Answer 17

Tu puedes hacer:

select concat(''drop table if exists '', table_name, '' cascade;'')
  from information_schema.tables;

A continuación, ejecute las consultas generadas. Se eliminarán todas las tablas en la base de datos actual.

Here hay algo de ayuda sobre el comando drop table .

Answer 18

Un liner para eliminar todas las tablas de una base de datos determinada:

echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse ''show tables'' {}| xargs -I[] mysql -e ''SET FOREIGN_KEY_CHECKS=0; drop table []'' {}"

Al ejecutar esto, se eliminarán todas las tablas de la base de datos DATABASE_NAME.

Y algo bueno de esto es que el nombre de la base de datos solo se escribe explícitamente una vez.

Answer 19

Uso lo siguiente con un servidor MSSQL:

if (DB_NAME() = ''YOUR_DATABASE'') 
begin
    while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE=''FOREIGN KEY''))
    begin
         declare @sql nvarchar(2000)
         SELECT TOP 1 @sql=(''ALTER TABLE '' + TABLE_SCHEMA + ''.['' + TABLE_NAME + ''] DROP CONSTRAINT ['' + CONSTRAINT_NAME + '']'')
         FROM information_schema.table_constraints
         WHERE CONSTRAINT_TYPE = ''FOREIGN KEY''
         exec (@sql)
         PRINT @sql
    end

    while(exists(select 1 from INFORMATION_SCHEMA.TABLES))
    begin
         declare @sql2 nvarchar(2000)
         SELECT TOP 1 @sql2=(''DROP TABLE '' + TABLE_SCHEMA + ''.['' + TABLE_NAME + '']'')
         FROM INFORMATION_SCHEMA.TABLES
        exec (@sql2)
        PRINT @sql2
    end
end
else
    print(''Only run this script on the development server!!!!'')

Reemplace YOUR_DATABASE con el nombre de su base de datos o elimine la declaración IF completa (me gusta la seguridad adicional).

Answer 20

La mejor solución para mi hasta ahora

Seleccione Base de datos -> Clic derecho -> Tareas -> Generar scripts - se abrirá el asistente para generar scripts. Después de elegir los objetos en la opción establecer secuencias de comandos, haga clic en el botón Avanzado . Bajo "Script DROP and CREATE" seleccione Script DROP .

Ejecutar guión.

Answer 21

DB="your database name" /
    && mysql $DB < "SET FOREIGN_KEY_CHECKS=0" /
    && mysqldump --add-drop-table --no-data $DB | grep ''DROP TABLE'' | grep -Ev "^$" | mysql $DB /
    && mysql $DB < "SET FOREIGN_KEY_CHECKS=1"