Drop all tables whose names begin with a certain string

by

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.

More Related Contents:

  1. Getting result of dynamic SQL into a variable for sql-server
  2. How to use table variable in a dynamic sql statement?
  3. Dynamic SQL to generate column names?
  4. T-SQL: How to use parameters in dynamic SQL?
  5. SQL Server – Best way to get identity of inserted row?
  6. How to update two tables in one statement in SQL Server 2005?
  7. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  8. Is the NOLOCK (Sql Server hint) bad practice?
  9. Creating table names that are reserved words/keywords in MS SQL Server [closed]
  10. Dynamic SQL – EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)
  11. Get all dates between two dates in SQL Server
  12. SQL – Subtracting a depleting value from rows
  13. Stored procedure or function expects parameter which is not supplied
  14. Pass table as parameter into sql server UDF
  15. SQL Server Convert Varchar to Datetime
  16. There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key
  17. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  18. Compare DATETIME and DATE ignoring time portion
  19. Fastest way to determine if record exists
  20. SQL – Query to get server’s IP address
  21. How to concatenate many rows with same id in sql?
  22. How do I edit a table in order to enable CASCADE DELETE?
  23. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  24. Query times out from web app but runs fine from management studio
  25. Passing dynamic order by in stored procedure
  26. How do I compare 2 rows from the same table (SQL Server)?
  27. Add a column to existing table and uniquely number them on MS SQL Server
  28. How to execute a stored procedure inside a select query
  29. What is the most appropriate data type for storing an IP address in SQL server? [duplicate]
  30. SQL Inner Join On Null Values

Leave a Comment