Drop Every Database Table - SQL Server

Overview¶

The following script will generate a set of SQL statements that will drop every table in the current database. As a precursor to dropping the tables, every foreign key is dropped first.

SQL¶

select distinct
    DropCode = 'alter table [' + ots.name + '].[' + OnTable.name + '] drop constraint [' + fk.name + ']'

from 
    sys.foreign_keys fk

    inner join sys.objects OnTable
        on fk.parent_object_id = OnTable.object_id

    inner join sys.schemas ots
        on OnTable.schema_id = ots.schema_id

union 
select 
    DropCode = 'drop table [' + s.name + '].[' + t.name + ']'

from 
    sys.tables t
    
    inner join sys.schemas s
        on t.schema_id = s.schema_id