How can I drop all tables in my database ?
1) In one MYSQL command
2) Without destroying and creating the database ?
Thanks
+5
A:
Try something like this:
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
Neat little trick, and it works for me.
Originally suggested here.
Michael Gorsuch
2010-09-18 13:59:35
Note that this is to be run from system command line, not as MySQL statement.
Mchl
2010-09-18 14:45:35
I can only run mysql statements, so I guess this doesn't work
Patrick
2010-09-19 19:31:28
A:
Try this:
SELECT name INTO #tables from sys.objects where type = 'U'
while (SELECT count(1) FROM #tables) > 0
begin
declare @sql varchar(max)
declare @tbl varchar(255)
SELECT top 1 @tbl = name FROM #tables
SET @sql = 'drop table ' + @tbl
exec(@sql)
DELETE FROM #tables where name = @tbl
end
DROP TABLE #tables;
Got this from here. Quick and dirty, it says. It certainly is dirty. ;-)
Evert
2010-09-18 13:59:53
More likely because it's not MySQL. A procedure like this can be created in MySQL, using information.schema in place of sys.objects (and of course MySQL compatible syntax for variables)
Mchl
2010-09-18 14:33:14
A:
Here a example, but it is for MS SQL Server:
USE myBD -- user DB
DECLARE tables_cursor CURSOR
FOR SELECT name FROM sys.objects WHERE type = 'U' --carefull here
OPEN tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS != -1)
BEGIN
EXEC ('DROP TABLE ' + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
DEALLOCATE tables_cursor
kofucii
2010-09-18 14:29:48