Drop All Tables Whose Names Begin with a Certain String

Drop all tables whose names begin with a certain string

You may need to modify the query to include the owner if there's more than one in the database.

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

This is cleaner than using a two-step approach of generate script plus run. But one advantage of the script generation is that it gives you the chance to review the entirety of what's going to be run before it's actually run.

I know that if I were going to do this against a production database, I'd be as careful as possible.

Edit Code sample fixed.

How to delete all MySQL tables beginning with a certain prefix?

There's no single statement to do that.

The simplest approach is to generate a set of statements, and execute them individually.

A simple query can generate the statements for you:

 SELECT CONCAT('DROP TABLE `',t.table_schema,'`.`',t.table_name,'`;') AS stmt
   FROM information_schema.tables t
  WHERE t.table_schema = 'mydatabase'
    AND t.table_name LIKE 'aggregate\_temp%' ESCAPE '\\'
  ORDER BY t.table_name

That just returns a rowset, but the rows conveniently contain the exact SQL statements you need to execute. (Note that information_schema is a builtin database that contains metadata. You'd just need to replace mydatabase with the name of the database you want to drop tables from.

Save the resultset from this query as a plain text file, remove any heading line, and voila, you've got a script you can execute in your SQL client.

There's no need for an elaborate stored procedure.

SQL query to delete all rows from tables where the table name contains a certain string

So, after combining the code from the possible duplicate question, and the answer from Greg, here's the result that worked best:

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'truncate table ' + quotename(OBJECT_SCHEMA_NAME(object_id)) + '.' + 
quotename(name)
FROM sys.tables
WHERE name like '%PMO%'
ORDER BY name;

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

Thanks to everyone for your help!!!

DROP all tables starting with EXT_ in Oracle SQL

You could use a short anonymous block to do this.

BEGIN
  FOR c IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'EXT_%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name;
  END LOOP;
END;

SQL: deleting tables with prefix

You cannot do it with just a single MySQL command, however you can use MySQL to construct the statement for you:

In the MySQL shell or through PHPMyAdmin, use the following query

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_name LIKE 'myprefix_%';

This will generate a DROP statement which you can than copy and execute to drop the tables.

EDIT: A disclaimer here - the statement generated above will drop all tables in all databases with that prefix. If you want to limit it to a specific database, modify the query to look like this and replace database_name with your own database_name:

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_schema = 'database_name' AND table_name LIKE 'myprefix_%';

Find all tables whose name ends with a certain suffix

Try this:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.tables 
WHERE TABLE_NAME LIKE '%_History'

OR

SELECT name
FROM sys.tables
WHERE name LIKE '%_History'

---

Related Topics