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!
/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
/*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'
If you dont want to miss any saved Diagrams…
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;