Page History: Delete Data from All Tables - SQL Server

Compare Page Revisions

8 7 6 5 4 3 2 1 0 Current 8 7 6 5 4 3 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Tue, Jun 13, 2017, 9:05 AM

Overview¶

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

SQL¶

/*--- This SQL generates a set of SQL statements to disable every foreign key in the database ---*/
select distinct
     GroupNum = 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

/*--- This SQL generates a set of SQL statements to delete data from every table ---*/
union select distinct
     GroupNum = 20
    ,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 o.name not in ('__MigrationHistory', '__EFMigrationsHistory')

/*--- This SQL generates a set of SQL statements to re-enable every foreign key in the database which was originally enabled ---*/
union select distinct
     GroupNum = 30
    ,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 
    GroupNum