Eduardo Sojo's Blog

You can easily delete tables from a SQL Server database taking in consider a query to select the tables which apply for deletion. We can build the DROP TABLE sentence dinamically for each table found in the query result. It means that we could change this query to execute other tasks iterating in a collection result

The following script shows how achieve this goal:

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
--Query to select and build DROP TABLE sentence
SELECT 'DROP TABLE '+FLD AS FIELD FROM
(SELECT '['+TABLE_SCHEMA+'].['+TABLE_NAME+']' AS FLD
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
TABLE_SCHEMA <> 'dbo') AS TBL

OPEN

@Cursor FETCH NEXT FROM @Cursor INTO @Sql

PRINT 'THE FOLLOWING TABLES WERE DELETED:'
PRINT '---------------------------------'

WHILE
	(@@FETCH_STATUS = 0)
BEGIN
	Exec SP_EXECUTESQL @Sql
	PRINT '- ' + @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE
@Cursor DEALLOCATE @Cursor

GO

Regards,

[This post in spanish]