SQL Server, Empty your Database & Reset Identity Columns **made Simple

This is a SQL Script that Cleans your Database Records & resets Identity Columns, and it is all in 6 lines!

[sourcecode lang=”sql”]/*Disable Constraints & Triggers*/
exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL'[/sourcecode]
[sourcecode lang=”sql”]
/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable ‘DELETE ?'[/sourcecode]
[sourcecode lang=”sql”]
/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL'[/sourcecode]
[sourcecode lang=”sql”]
/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable ‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1 BEGIN DBCC CHECKIDENT (”?”,RESEED,0) END'[/sourcecode]

If you dont want to miss any saved Diagrams…
[sourcecode lang=”sql”]
exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’;
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’;

CREATE TABLE #tmpDiagrams
(
[name] [sysname] NOT NULL,
[principal_id] [int] NOT NULL,
[diagram_id] [int] NOT NULL,
[version] [int] NULL,
[definition] [varbinary](max) NULL
)

INSERT INTO #tmpDiagrams SELECT * FROM sysdiagrams;

/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable ‘DELETE ?’;

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’;
exec sp_MSforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL’;

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable ‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1 BEGIN DBCC CHECKIDENT (”?”,RESEED,0) END’;
PRINT ‘### Cleared All Tables ###’

INSERT INTO sysdiagrams ([name],[principal_id],[version],[definition]) SELECT [name],[principal_id],[version],[definition] FROM #tmpDiagrams

DROP TABLE #tmpDiagrams;
[/sourcecode]