Drop SQL tables based on table name

Here is a little SQL snippet for Microsoft SQL Server to drop (delete) tables based on their name. I have the custom of prefixing all old tables I don't use with a Z, so they drop to the bottom of the list. I finally wanted to delete them all, so here is a nice script that accomplished that. You must specify the specific database by changing 'YourDatabaseName.'



-- Drop all tables based on a specific name
-- Currently set to drop all tables starting with z
-- This likely won't work for tables that have foreign keys...

DECLARE @TABLE_NAME varchar(255)
declare @SQL varchar(255)

DECLARE CUR CURSOR FOR

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='YourDatabaseName'
and TABLE_NAME like 'z%'
order by TABLE_NAME


OPEN CUR

FETCH NEXT FROM CUR INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'drop table if exists ['+@TABLE_NAME + ']'

PRINT @SQL
--EXEC (@SQL) -- uncomment this line to actually delete your tables

FETCH NEXT FROM CUR INTO @TABLE_NAME
END

CLOSE CUR

DEALLOCATE CUR