Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Drop All Tables - SQL Server

RSS
Modified on Wed, Aug 09, 2017, 4:04 PM by Administrator Categorized as SQL Server

Overview

The following SQL statement will generate a set of SQL statements which will drop all tables.

See Also


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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.