· 8 years ago · Jan 01, 2018, 12:28 PM
1SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
2FROM information_schema.tables
3WHERE table_schema = 'MyDatabaseName';
4
5SET FOREIGN_KEY_CHECKS = 0
6
7SET FOREIGN_KEY_CHECKS = 1
8
9SET FOREIGN_KEY_CHECKS = 0;
10drop table if exists customers;
11drop table if exists orders;
12drop table if exists order_details;
13SET FOREIGN_KEY_CHECKS = 1;
14
15DROP PROCEDURE IF EXISTS `drop_all_tables`;
16
17DELIMITER $$
18CREATE PROCEDURE `drop_all_tables`()
19BEGIN
20 DECLARE _done INT DEFAULT FALSE;
21 DECLARE _tableName VARCHAR(255);
22 DECLARE _cursor CURSOR FOR
23 SELECT table_name
24 FROM information_schema.TABLES
25 WHERE table_schema = SCHEMA();
26 DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
27
28 SET FOREIGN_KEY_CHECKS = 0;
29
30 OPEN _cursor;
31
32 REPEAT FETCH _cursor INTO _tableName;
33
34 IF NOT _done THEN
35 SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
36 PREPARE stmt1 FROM @stmt_sql;
37 EXECUTE stmt1;
38 DEALLOCATE PREPARE stmt1;
39 END IF;
40
41 UNTIL _done END REPEAT;
42
43 CLOSE _cursor;
44 SET FOREIGN_KEY_CHECKS = 1;
45END$$
46
47DELIMITER ;
48
49call drop_all_tables();
50
51DROP PROCEDURE IF EXISTS `drop_all_tables`;
52
53use `dbName`; --your db name here
54 SET FOREIGN_KEY_CHECKS = 0;
55 SET @tables = NULL;
56 SET GROUP_CONCAT_MAX_LEN=32768;
57
58 SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables
59 FROM information_schema.tables
60 WHERE table_schema = (SELECT DATABASE());
61 SELECT IFNULL(@tables, '') INTO @tables;
62
63 SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
64 PREPARE stmt FROM @tables;
65 EXECUTE stmt;
66 DEALLOCATE PREPARE stmt;
67 SET FOREIGN_KEY_CHECKS = 1;
68
69SELECT CONCAT('DROP TABLE IF EXISTS `', table_schema, '`.`', table_name, '`;')
70 FROM information_schema.tables
71 WHERE table_schema = 'dbName'; --your db name here
72
73DROP PROCEDURE IF EXISTS `drop_all_tables`;
74
75DELIMITER $$
76CREATE PROCEDURE `drop_all_tables`()
77BEGIN
78 DECLARE _done INT DEFAULT FALSE;
79 DECLARE _tableName VARCHAR(255);
80 DECLARE _cursor CURSOR FOR
81 SELECT table_name
82 FROM information_schema.TABLES
83 WHERE table_schema = SCHEMA();
84 DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
85
86 OPEN _cursor;
87
88 REPEAT FETCH _cursor INTO _tableName;
89
90 IF NOT _done THEN
91 SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
92 PREPARE stmt1 FROM @stmt_sql;
93 EXECUTE stmt1;
94 DEALLOCATE PREPARE stmt1;
95 END IF;
96
97 UNTIL _done END REPEAT;
98
99 CLOSE _cursor;
100
101END$$
102
103DELIMITER ;
104
105call drop_all_tables();
106
107DROP PROCEDURE IF EXISTS `drop_all_tables`;
108
109select concat('drop table if exists ', table_name, ' cascade;')
110 from information_schema.tables;
111
112SET GROUP_CONCAT_MAX_LEN = 10000000;
113SELECT CONCAT('SET FOREIGN_KEY_CHECKS=0;n',
114 GROUP_CONCAT(CONCAT('DROP TABLE IF EXISTS `', table_name, '`')
115 SEPARATOR ';n'),
116 ';nSET FOREIGN_KEY_CHECKS=1;')
117FROM information_schema.tables
118WHERE table_schema = 'SchemaName';
119
120echo "SET FOREIGN_KEY_CHECKS = 0;" > temp.txt;
121mysqldump -u[USER] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP >> temp.txt;
122echo "SET FOREIGN_KEY_CHECKS = 1;" >> temp.txt;
123mysql -u[USER] -p[PASSWORD] [DATABASE] < temp.txt;
124
125( mysqldump --add-drop-table --no-data -u root -p database | grep 'DROP TABLE' ) > ./drop_all_tables.sql
126mysql -u root -p database < ./drop_all_tables.sql
127
128host=$1
129dbName=$2
130user=$3
131password=$4
132
133if [ -z "$1" ]
134then
135 host="localhost"
136fi
137
138# drop all the tables in the database
139for 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
140
141$pdo = new PDO('mysql:dbname=YOURDB', 'root', 'root');
142
143$pdo->exec('SET FOREIGN_KEY_CHECKS = 0');
144
145$query = "SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
146 FROM information_schema.tables
147 WHERE table_schema = 'YOURDB'";
148
149foreach($pdo->query($query) as $row) {
150 $pdo->exec($row[0]);
151}
152
153$pdo->exec('SET FOREIGN_KEY_CHECKS = 1');
154
155DATABASE_TO_EMPTY="your_db_name";
156{ echo "SET FOREIGN_KEY_CHECKS = 0;" ;
157 mysql "$DATABASE_TO_EMPTY" --skip-column-names -e
158 "SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
159 FROM information_schema.tables WHERE table_schema = '$DATABASE_TO_EMPTY';";
160 } | mysql "$DATABASE_TO_EMPTY"
161
162#!/bin/bash
163
164DB_HOST=xxx
165DB_USERNAME=xxx
166DB_PASSWORD=xxx
167DB_NAME=xxx
168
169CMD="mysql -sN -h ${DB_HOST} -u ${DB_USERNAME} -p${DB_PASSWORD} ${DB_NAME}"
170
171# Generate the drop statements
172TMPFILE=/tmp/drop-${RANDOM}.sql
173echo 'SET FOREIGN_KEY_CHECKS = 0;' > ${TMPFILE}
174${CMD} $@ >> ${TMPFILE} << ENDD
175 SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
176 FROM information_schema.tables
177 WHERE table_schema = '${DB_NAME}';
178ENDD
179echo 'SET FOREIGN_KEY_CHECKS = 1;' >> ${TMPFILE}
180
181# Warn what we are about to do
182echo
183cat ${TMPFILE}
184echo
185echo "Press ENTER to proceed (or Ctrl-C to abort)."
186read
187
188# Run the SQL
189echo "Dropping tables..."
190${CMD} $@ < ${TMPFILE}
191echo "Exit status is ${?}."
192rm ${TMPFILE}
193
194echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"
195
196mysql -Nse 'show tables' DB_NAME | while read table; do mysql -e "SET FOREIGN_KEY_CHECKS=0; truncate table `$table`" DB_NAME; done
197
198if (DB_NAME() = 'YOUR_DATABASE')
199begin
200 while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
201 begin
202 declare @sql nvarchar(2000)
203 SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
204 FROM information_schema.table_constraints
205 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
206 exec (@sql)
207 PRINT @sql
208 end
209
210 while(exists(select 1 from INFORMATION_SCHEMA.TABLES))
211 begin
212 declare @sql2 nvarchar(2000)
213 SELECT TOP 1 @sql2=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
214 FROM INFORMATION_SCHEMA.TABLES
215 exec (@sql2)
216 PRINT @sql2
217 end
218end
219else
220 print('Only run this script on the development server!!!!')