SQL Server 2008 delete all tables under special schema

This will generate all the DROP TABLE statements for you and PRINT the SQL statement out.
You can then validate it’s what you expect before copying and executing. Just make sure you are 100% sure…maybe take a backup first 🙂

DECLARE @SqlStatement NVARCHAR(MAX)
SELECT @SqlStatement = 
    COALESCE(@SqlStatement, N'') + N'DROP TABLE [DBO1].' + QUOTENAME(TABLE_NAME) + N';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DBO1' and TABLE_TYPE = 'BASE TABLE'

PRINT @SqlStatement