Delete Data from All Tables - SQL Server

Overview

The following SQL statement will generate a set of SQL statements which will delete all data from all tables.

NOTE

This will re-seed all IDENTITY columns in the database.

See Also


SQL

declare
    @DeleteMigrationHistory bit = 0 /* TODO */


declare
    @DoubleBar varchar(100) = '/*===============================================================================================*/'
    ,@SinglBar varchar(100) = '/*-----------------------------------------------------------------------------------------------*/'

/*===============================================================================================*/
/*   Disable 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 + '] NOCHECK 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'
    and ObjectProperty(fk.constid,'CnstIsDisabled') = 0

/*===============================================================================================*/
/*   Delete Data                                                                                 */
/*===============================================================================================*/
union
select distinct
     Sort1 = 20
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = @DoubleBar
union
select distinct
     Sort1 = 21
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = '/*  Delete Data                                                                                  */'
union
select distinct
     Sort1 = 22
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = @DoubleBar
union
select distinct
     Sort1 = 23
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode  = 'delete from [' + 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'))

/*===============================================================================================*/
/*   Re-Seed IDENTITY Fields                                                                     */
/*===============================================================================================*/
union
select distinct
     Sort1 = 30
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = @DoubleBar
union
select distinct
     Sort1 = 31
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = '/*  Re-seed IDENTITY Fields                                                                      */'
union
select distinct
     Sort1 = 32
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = @DoubleBar
union
select distinct
     Sort1 = 33
    ,Sort2 = '[' + s.name + '].[' + o.name + ']'
    ,Sort3 = 10
    ,SqlCode = 'DBCC CHECKIDENT (''[' + s.name + '].[' + o.name + ']'', RESEED, 0);'
from
    sys.objects o

    inner join sys.columns c 
        on o.object_id = c.object_id

    inner join sys.schemas s
        on o.schema_id = s.schema_id

WHERE 1=1
    and o.name not in ('__MigrationHistory', '__EFMigrationsHistory')
    and o.type = 'U'
    and c.is_identity = 1
union
select distinct
     Sort1 = 33
    ,Sort2 = '[' + s.name + '].[' + o.name + ']'
    ,Sort3 = 11
    ,SqlCode = 'GO'
from
    sys.objects o

    inner join sys.columns c 
        on o.object_id = c.object_id

    inner join sys.schemas s
        on o.schema_id = s.schema_id

WHERE 1=1
    and o.name not in ('__MigrationHistory', '__EFMigrationsHistory')
    and o.type = 'U'
    and c.is_identity = 1
/*===============================================================================================*/
/*   Re-enable Foreign Keys                                                                      */
/*===============================================================================================*/
union
select distinct
     Sort1 = 40
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = @DoubleBar
union
select distinct
     Sort1 = 41
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = '/*  Re-enabled Foreign Keys                                                                      */'
union
select distinct
     Sort1 = 42
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode = @DoubleBar
union 
select distinct
     Sort1 = 43
    ,Sort2 = ''
    ,Sort3 = 10
    ,SqlCode  = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name + '] CHECK 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'
    and ObjectProperty(fk.constid,'CnstIsDisabled') = 0

order by 
    Sort1, Sort2