Skip to content

Instantly share code, notes, and snippets.

@Deepankar1993

Deepankar1993/MSSQL_DELETE_ALL_DATA.sql

Forked from rvegajr/MSSQL_DELETE_ALL_DATA.sql
Created January 17, 2021 19:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Deepankar1993/c1c8f72012ea8f0c54a6384887c57dc8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Deepankar1993/c1c8f72012ea8f0c54a6384887c57dc8 to your computer and use it in GitHub Desktop.
Download ZIP
MSSQL Delete all rows from every table and reseed
Raw
MSSQL_DELETE_ALL_DATA.sql
--Disable all check constraints on all tables
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
--Disable all triggers on all tables
EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
--Delete all data from all tables
EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'
GO
--Enable all constraints on all tables
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO
--Enable all triggers on all tables
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
--Ressed the identity columns seed value
EXEC sp_MSForEachTable 'IF(OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1) DBCC CHECKIDENT(''?'', RESEED, 0)'
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment