Submitted by greggles on
Let's say you need to drop all tables in a mysql database. How do you do that?
You could use a gui, but that's not fun.
You're a shell jockey so you want a commandline:
mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | \ gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname
(that's all one line, but if I do it as a line then it screws up my theme - go figure).
This assumes that you are running in passwordless mode. See "man mysql" for tips on how to pass in passwords in another manner.
What this does is
- connect to a specific mysql database and execute the command for showing tables
- find lines that match "Tables_in" and not show them
- find lines that match the + character and not show them
- use gawk to print out the words "drop table" followed by the table name (which is in $1) and then a semicolon
- pipe all of that back to the database you got the list from to drop those tables
Fun stuff and very handy!
Category:
People Involved:
timeline:
- Log in to post comments
Comments
Jpsy replied on Permalink
for Windows (using grep and gawk from UnxUtils)
Here's how you can do it on Windows:
Download the latest UnxUtils ZIP from http://sourceforge.net/projects/unxutils
These are precompiled versions of basic Unix tools for Windows, including grep & gawk.
Unpack into some local folder (e.g. into C:\Program Files\UnxUtils)
Then use this code in a batch file (adapt the first 5 lines as needed):
set UnixUtilsPath=C:\Program Files\UnxUtils\usr\local\wbin\
set MySQLPath=C:\Program Files\MySQL\MySQL Server 5.0\bin\
set dbname=mydatabase
set usr=myusername
set pwd=mypassword
"%MySQLPath%mysql" -u%usr% -p%pwd% %dbname% -e "show tables" | "%UnixUtilsPath%grep" -v Tables_in | "%UnixUtilsPath%gawk" "{print \"drop table \" $1 \";\"}" | "%MySQLPath%mysql" -u%usr% -p%pwd% %dbname%
Enjoy!
Jpsy
Anonymous replied on Permalink
awesome command line tip!
Thanks!
Rex M replied on Permalink
match table name to drop in phpmyadmin sql?
I found your command line information via web search and curious if you could tell me a way to drop table "table_*" from the phpmysqladadmin, as my ISP only allows web interface to the mysql backend.
siliconmeadow replied on Permalink
Dropping tables within phpMyAdmin
As Greg said, the previous comment drops the whole database. If you can't use Greg's command line solution and have to use phpMyAdmin to drop all tables:
max replied on Permalink
Awesome, that's precisely
Awesome, that's precisely what I was looking for, thank you!
tcar replied on Permalink
Go to phpMyAdmin
Go to phpMyAdmin homepage
Click 'Databases' to get a list of your databases
Check the checkbox next to the database you want to drop
Click the icon with the X (it's a red x) below the list of databases (mouse rollover brings up 'Drop')
Click 'Yes' when you get warning 'You are about to DESTROY a complete database!'.
Voila! database dropped.
not exactly
Well, that requires phpmyadmin. The point of my script is not to use a gui.
Also, you just dropped the whole database. The point of my script is to delete the tables even if you don't have permission to drop/create a database. That's handy, for example, with a web application where you want a "clean slate".
Rex M replied on Permalink
TCAR ?
TCAR,
the former admin placed multiple instances in one large mysql db, each instance has a "table_prefix_"
Don't won't to drop the entire db, just the tables from one instance.
try breaking apart the script I gave you
In the middle are a bunch of "grep" commands which limit the set of tables to work with. Just add in another "grep table_prefix |" to the middle of the command in the original post and that should work.
weatheredwatcher replied on Permalink
Pingback
nsslive &ra... replied on Permalink
Pingback
Matteo replied on Permalink
Tip: instead of using: |
Tip: instead of using:
| grep -v Tables_in | grep -v "+"
you can comfortably use the nice mysql command options --silent and --skip-column-names; so the command line would became:
mysql --silent --skip-column-names -u uname dbname -e "show tables" | \
gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname
:-)
seagen replied on Permalink
hi dude.....the two option
hi dude.....the two option about --silent and --skip-colum-names ,both are so nice.....cool...
thanks for your tip ;)
Jamie replied on Permalink
Just what I was looking for
Yep, that is what I was looking to accomplish with my demo site. Thanks so much for this blog post!
Удалить все таб... replied on Permalink
Pingback
Lon F. Binder replied on Permalink
Better linux approach
A little longer, but also a little more efficient because it does the drop (of all tables) in one shot:
mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD -BNe "show tables" YOUR_DBSCHEMA_NAME | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD YOUR_DBSCHEMA_NAME
Raja Shahed replied on Permalink
Drop all tables from a MySQL database in one shot
Deleting every thing is easier than Creating. Access to your Mysql tables thru PHPMYADMIN. Click the database at the left hand side to display all tables at the right hand side frame. Now select tables to delete by clicking their regarding checkbox. At the bottom or under the tables listing there is a Drop down list : With Selected - Choose DROP. pops a confirmation window. Click OK.Thats all. Some web hosters do not allow you creating or deleting a DB - in such case it comes handy. In this way you can delete - some or all tables from a database.
phpmyadmin: do not want!
Why would I bother installing a webgui to the database when there is a command line?
Especially when that webgui to the database is one which has had security updates - so now I have to update my core software and phpmyadmin...no thanks.
RaduKing replied on Permalink
It would all work nice if there wasn't for this:
ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails
any idea how to fix this ?
foreign keys
You have relationships on keys between tables. The script doesn't take those into account and is trying to drop a table that is required by another table.
You will probably have to delete them in a more specific order OR do some sort of option to force the delete like from this stack overflow article.
Or, if you run my command from above two or three times it should eventually work because it eventually drops the tables in the right order.
Как удалить все... replied on Permalink
Pingback
Tobias Sjösten replied on Permalink
A variant
You could also try a shorter (number of characters) variant which uses the fact that mysqldump by default adds a DROP instruction:
$ mysqldump -uroot -p dbase|grep ^DROP|mysql -uroot -p dbase
Anonymous replied on Permalink
The --no-data option is
The --no-data option is useful here.
Sharing My Read... replied on Permalink
Pingback
thetoast replied on Permalink
Thanks for that, worked like
Thanks for that, worked like a charm.
Tobias Sjösten replied on Permalink
Drush command
If you're using Drupal and Drush, and I suspect you are, then you could use my sql-drop command:
http://github.com/tobiassjosten/drush-sql-drop
Thanks, Tobias. I posted this
Thanks, Tobias. I posted this back on 2007 when drush was newborn and did very little. I also wanted something that would work in limited environments where I might not be able to install Drush and something that would work regardless of whether the database was being used for Drupal.
Any chance you're going to incorporate that into the main drush?
Tobias Sjösten replied on Permalink
I would love to! It was
I would love to! It was basically done as a proof of concept and could probably be improved a lot. But I'll go ahead and bring that up in the Drush issue queue.
Delete all tab... replied on Permalink
Pingback
Alejandro Senges replied on Permalink
Drop Multiple Tables
Your first script worked fine!!! I just added some minimal changes to fit my specific requirements and put it into a bash file like this
!bin/bash
mysql -u dbuser --password=dbpasswd dbname -e "show tables like 'prefix%'" | grep -v Tables_in | grep -v "+" | awk '{print "SET FOREIGN_KEY_CHECKS = 0; drop table " $1 ";"}' | mysql -u dbuser --password=dbpasswd dbname
Kevin Kaland replied on Permalink
Worked like a charm
I just needed this to clear a database, and for some reason the oracle guided me here. Coincidence? Probably not, but anyway I guess the bubble did me well here, and yeah...it worked. I made a similar edit to Alejandro to add the password.
So, thanks!
@wizonesolutions