Drop all tables whose names begin with a certain string

Question

How can I drop all tables whose names begin with a given string    I think this can be done with some dynamic SQL and the INFORMATION SCHEMA tables

User · Answer

I had to do a slight derivation on Xenph Yan s answer I suspect because I had tables not in the default schema     SELECT  DROP TABLE Databasename schema     TABLE NAME  FROM INFORMATION SCHEMA TABLES  WHERE TABLE NAME LIKE  strmatch

User · Answer

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

User · Answer

On Oracle XE this works   SELECT  DROP TABLE       TABLE NAME         FROM USER TABLES WHERE TABLE NAME LIKE  YOURTABLEPREFIX     Or if you want to remove the constraints and free up space as well  use this   SELECT  DROP TABLE       TABLE NAME       cascade constraints PURGE   FROM USER TABLES WHERE TABLE NAME LIKE  YOURTABLEPREFIX     Which will generate a bunch of DROP TABLE cascade constraints PURGE statements     For VIEWS use this   SELECT  DROP VIEW       VIEW NAME         FROM USER VIEWS WHERE VIEW NAME LIKE  YOURVIEWPREFIX

User · Answer

EXEC sp MSforeachtable  if PARSENAME     1  like    CertainString    DROP TABLE      Edit   sp MSforeachtable is undocumented hence not suitable for production because it s behavior may vary depending on MS SQL version

User · Answer

This worked for me   DECLARE  sql NVARCHAR MAX    N     SELECT  sql      DROP TABLE          QUOTENAME s name              QUOTENAME t name            FROM sys tables AS t     INNER JOIN sys schemas AS s     ON t  schema id    s  schema id       WHERE t name LIKE  something     PRINT  sql  -- EXEC sp executesql  sql

User · Answer

This will get you the tables in foreign key order and avoid dropping some of the tables created by SQL Server  The t Ordinal value will slice the tables into dependency layers   WITH TablesCTE SchemaName  TableName  TableID  Ordinal  AS       SELECT OBJECT SCHEMA NAME so object id  AS SchemaName          OBJECT NAME so object id  AS TableName          so object id AS TableID          0 AS Ordinal     FROM sys objects AS so     WHERE so type    U          AND so is ms Shipped   0         AND OBJECT NAME so object id          LIKE  MyPrefix        UNION ALL     SELECT OBJECT SCHEMA NAME so object id  AS SchemaName          OBJECT NAME so object id  AS TableName          so object id AS TableID          tt Ordinal   1 AS Ordinal     FROM sys objects AS so         INNER JOIN sys foreign keys AS f             ON f parent object id   so object id                 AND f parent object id    f referenced object id         INNER JOIN TablesCTE AS tt             ON f referenced object id   tt TableID     WHERE so type    U          AND so is ms Shipped   0         AND OBJECT NAME so object id          LIKE  MyPrefix     SELECT DISTINCT t Ordinal  t SchemaName  t TableName  t TableID FROM TablesCTE AS t     INNER JOIN               SELECT             itt SchemaName AS SchemaName              itt TableName AS TableName              itt TableID AS TableID              Max itt Ordinal  AS Ordinal         FROM TablesCTE AS itt         GROUP BY itt SchemaName  itt TableName  itt TableID       AS tt         ON t TableID   tt TableID             AND t Ordinal   tt Ordinal ORDER BY t Ordinal DESC  t TableName ASC

User · Answer

SELECT  DROP TABLE      TABLE NAME        FROM INFORMATION SCHEMA TABLES  WHERE TABLE NAME LIKE   prefix      This will generate a script   Adding clause to check existence of table before deleting   SELECT  IF OBJECT ID      TABLE NAME        IS NOT NULL BEGIN DROP TABLE      TABLE NAME      END    FROM INFORMATION SCHEMA TABLES  WHERE TABLE NAME LIKE   prefix

User · Answer

select  DROP TABLE     name from sysobjects where type    U  and sysobjects name like   test     -- Test is the table name

User · Answer

CREATE PROCEDURE usp GenerateDROP      Pattern AS varchar 255        PrintQuery AS bit       ExecQuery AS bit AS BEGIN     DECLARE  sql AS varchar max       SELECT  sql   COALESCE  sql         DROP TABLE      TABLE NAME         CHAR 13    CHAR 10      FROM INFORMATION SCHEMA TABLES     WHERE TABLE NAME LIKE  Pattern      IF  PrintQuery   1 PRINT  sql     IF  ExecQuery   1 EXEC   sql  END

User · Answer

In case of temporary tables  you might want to try   SELECT  DROP TABLE      t name        FROM tempdb sys tables t WHERE t name LIKE   prefix

User · Answer

I saw this post when I was looking for mysql statement to drop all WordPress tables based on  Xenph Yan here is what I did eventually   SELECT CONCAT    DROP TABLE     TABLE NAME          AS query FROM INFORMATION SCHEMA TABLES WHERE TABLE NAME LIKE   wp      this will give you the set of drop queries for all tables begins with wp

User · Answer

Here is my solution   SELECT CONCAT  DROP TABLE     TABLE NAME        FROM INFORMATION SCHEMA TABLES  WHERE TABLE NAME LIKE  TABLE PREFIX GOES HERE      And of course you need to replace TABLE PREFIX GOES HERE with your prefix

User · Answer

Xenph Yan s answer was far cleaner than mine but here is mine all the same   DECLARE  startStr AS Varchar  20  SET  startStr    tableName   DECLARE  startStrLen AS int SELECT  startStrLen   LEN  startStr   SELECT  DROP TABLE     name FROM sysobjects WHERE type    U  AND LEFT name   startStrLen     startStr   Just change tableName to the characters that you want to search with

User · Answer

I would like to post my proposal of the solution which DROP  not just generate and select a drop commands  all tables based on the wildcard  e g   quot table 20210114 quot   older than particular amount of days  DECLARE       drop command NVARCHAR MAX             system time date       table date nvarchar 8        older than int   7      Set  system time    select getdate   -  older than  Set  table date    SELECT CONVERT char 8    system time  112    SELECT  drop command    N DROP TABLE     QUOTENAME SCHEMA NAME schema id           QUOTENAME  Name         FROM  lt your database name gt  sys tables WHERE  Name  LIKE  table    AND RIGHT  Name  8   lt   table date  SELECT  drop command   EXEC sp executesql  drop command

User · Answer

SELECT  if object id       TABLE NAME        is not null begin drop table      TABLE NAME      end    FROM INFORMATION SCHEMA TABLES  WHERE TABLE NAME LIKE   prefix

[sql] Drop all tables whose names begin with a certain string

Examples related to sql

Examples related to sql-server

Examples related to dynamic-sql