Drop All Tables - SQL Server

Overview¶

See Also¶

• Delete Data from All Tables - SQL Server
  

SQL¶

declare
    @DeleteMigrationHistory bit = 0 /* TODO */


declare
    @DoubleBar varchar(100) = '/*===============================================================================================*/'

/*===============================================================================================*/
/*   Drop Foreign Keys                                                                           */
/*===============================================================================================*/
select distinct
     Sort1 = 10
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = @DoubleBar
union
select distinct
     Sort1 = 11
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = '/*  Disable Foreign Keys                                                                         */'
union
select distinct
     Sort1 = 12
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = @DoubleBar
union
select distinct
     Sort1 = 13
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode  = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name + '] DROP CONSTRAINT [' + foreignKey.name + ']'

from 

    sysforeignkeys fk

    inner join sysobjects foreignKey  
        on foreignKey.id = fk.constid

    inner join sys.objects onTable 
        on fk.fkeyid = onTable.object_id

    inner join sys.schemas onSchema
        on onSchema.schema_id = onTable.schema_id

    inner join sysobjects againstTable  
        on fk.rkeyid = againstTable.id

where 1=1
    AND againstTable.TYPE = 'U'
    AND onTable.TYPE = 'U'

/*===============================================================================================*/
/*   Drop Tables                                                                                 */
/*===============================================================================================*/
union
select distinct
     Sort1 = 20
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = @DoubleBar
union
select distinct
     Sort1 = 21
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = '/*  Drop Tables                                                                                  */'
union
select distinct
     Sort1 = 22
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = @DoubleBar
union
select distinct
     Sort1 = 23
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode  = 'drop table [' + s.name + '].[' + o.name + ']'
FROM
    sys.objects o
    inner join sys.schemas s
        on o.schema_id = s.schema_id
where 1=1
    and o.type = 'U'
    and (@DeleteMigrationHistory = 1 or o.name not in ('__MigrationHistory', '__EFMigrationsHistory'))

order by 
    Sort1, Sort2