Delete All Tables From a Schema in SQL Server

Recently, I’ve been migrating a lot of schemas from Oracle to SQL Server using the SQL Server Migration Assistant application. It is usually necessary to redo the table conversions a few times to get it right.
The following script is used to create DROP scripts to drop all the tables in a particular schema.

-- DROP ALL TABLES FROM A SCHEMA
select 'DROP TABLE ' + schema_name(t.schema_id) + '.' + t.name + ';' as drop_script
from sys.tables t
where schema_name(t.schema_id) = 'MY_SCHEMA' -- put schema name here
;

If you have foreign key constraints on any of the tables, those constraints must be dropped before dropping the tables via the script.

-- DROP CONSTRAINTS FROM ALL TABLES IN A SCHEMA
select 'ALTER TABLE ' + schema_name(t.schema_id) + '.' + t.name + ' DROP CONSTRAINT ' + fk.name + ';' as DROP_FK_SCRIPT
from sys.foreign_keys fk
inner join sys.tables t
on fk.parent_object_id = t.object_id
where schema_name(t.schema_id) = 'MY_SCHEMA' -- put schema name here
;