Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
- FROM information_schema.tables
- WHERE table_schema = 'MyDatabaseName';
- SET FOREIGN_KEY_CHECKS = 0
- SET FOREIGN_KEY_CHECKS = 1
- 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;
- 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`;
- 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;
- SELECT CONCAT('DROP TABLE IF EXISTS `', table_schema, '`.`', table_name, '`;')
- FROM information_schema.tables
- WHERE table_schema = 'dbName'; --your db name here
- 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`;
- select concat('drop table if exists ', table_name, ' cascade;')
- from information_schema.tables;
- 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';
- 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;
- ( 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
- 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
- $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');
- 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"
- #!/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}
- echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"
- mysql -Nse 'show tables' DB_NAME | while read table; do mysql -e "SET FOREIGN_KEY_CHECKS=0; truncate table `$table`" DB_NAME; done
- 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!!!!')
Add Comment
Please, Sign In to add comment