Drop all tables from a SQL Server schema (uncomment -- EXEC to apply changes)

gistfile1.txt

---------------------------------------------
-- SQLServer : DROP all tables from SCHEMA_NAME
---------------------------------------------

DECLARE @SCHEMA_NAME varchar(100)
SET @SCHEMA_NAME = 'my_schema'

DECLARE @cmd varchar(4000)

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 

select 'DROP TABLE ['+s.name+'].['+t.name+'];' FROM sys.Tables t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE s.name = @SCHEMA_NAME

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @cmd
WHILE @@FETCH_STATUS = 0
BEGIN
    -- EXEC (@cmd)
    PRINT @cmd
    FETCH NEXT FROM MY_CURSOR INTO @cmd
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

GO