2

How can we delete some specific tables from a database. For instance I have a database with more than 20.000 tables and I need to delete the one which contains some specific strings in their names. So how can I do it? Is there a way to get the all table names from the database?

Improve this question
3
  • Are you really DELETING TABLES based on content of ROWS in that table? What about referential integrity? Commented May 13, 2011 at 19:09
  • Yes I am going to delete tables but based on the their names not the content of rows. Also there is no referential integrity problem in my case. Commented May 13, 2011 at 19:14
  • 1
    The fact that you need to delete tables based on strings in their names indicates that you have a DB design problem. What's your actual use case? Commented Jun 15, 2011 at 22:30

3 Answers 3

Reset to default
7

You can get tables with certain names from information_schema.

This is how you get a list of the tables in your database:

select table_name from information_schema.tables;

With that in mind, you can generate a script to drop the tables you need:

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

Then copy that script and paste it on a SQL interpreter.

You could also filter tables based on their names or databases:

select concat('drop table ', table_name, ';')
  from information_schema.tables
 where table_name like 'abc%'
   and table_schema = 'myDatabase'; --db name
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Could you please more explain your last snippet of code? For instance I have a database called "myDatabase" and I want to delete the tables whose names start with "abc". Thanks a lot.
Also I tried following code to drop tables using phpmyadmin but it did work. Here is my code; DROP TABLE table_name FROM information_schema.tables WHERE table_name like '%abc%' and table_schema = 'myDatabase'
@Jay: Hi. I updated the answer to include what you've asked for. Delete all tables that start with 'abc' on 'myDatabase' database. Do keep in mind that this query will generate sort of like a script for you that you will run to actually delete your tables.
0

agree w/@n8wrl, but if you must you could use an IF ... THEN statement http://dev.mysql.com/doc/refman/5.0/en/if-statement.html to DROP TABLE

Improve this answer

Comments

0

The information_schema views are helpful for listing and filtering tables in any ANSI compliant database:

select *
  from information_schema.tables T
 where T.table_name like '%FILTER HERE%'

You can loop through and drop the relevant tables using dynamic SQL and a cursor through the above recordset.

Improve this answer

5 Comments

information_schema is an ANSI standard, nothing to do with Microsoft's T-SQL -- which is a good thing, because the OP is using MySQL, not MS SQL Server. Both MySQL and MS SQL Server support information_schema, however.
Duly noted. I knew OP was using MySql... Didn't know the correct terms :)
@Marnen Laibow-Koser: Agree. PostgreSQL also supports standard information schema access for its catalogs. I also think it's a very good thing. Just learning information schema instead of learning each metadata catalog available for each vendor. We need Oracle to join the party. :-)
how can I loop through your code? Also what exactly is information_schema and what refers to database name in your code?
Pablo: Oracle doesn't do information_schema? Amazing. (I've never used it, so I didn't know that.)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.