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

Page History: Delete Data from All Tables - SQL Server

Compare Page Revisions



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


Page Revision: Sat, Jun 10, 2017, 8:55 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

/*--- 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

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