> ## Content Index
> Fetch the complete content index at: https://blog.tsd.digital/llms.txt
> Use this file to discover other available public pages before exploring further.

# SQL Server Delete all data
- URL: https://blog.tsd.digital/sql-server-delete-all-data/
- Published: 2007-04-17T14:18:54.000Z
- Updated: 2007-04-17T14:18:54.000Z
- Author: Tim Gaunt
- Tags: SQL Server, Web Development, #Import 2025-04-01 05:36

I expect many people already know about this technique but I wanted to share it with those that don’t. The other day I needed to remove all data from a database before importing data from another database. I usually use DTS to copy the data across but knew that the database (one test) had conflicting ids so I decided deleting all the data out of the test database would be the best way to ensure all data’s up to date.

I found this useful little set of SQL at: [http://sqljunkies.com/WebLog/roman/archive/2006/03/03/18386.aspx](http://sqljunkies.com/WebLog/roman/archive/2006/03/03/18386.aspx?ref=blog.tsd.digital), there are two solutions propsed within the post and comments so here they both are:

### Delete the data without resetting the identities

\-- disable referential integrityEXECsp\_MSForEachTable'ALTERTABLE?NOCHECKCONSTRAINTALL'GOEXEC sp\_MSForEachTable 'DELETEFROM?'GO-- enable referential integrity againEXECsp\_MSForEachTable'ALTERTABLE?CHECKCONSTRAINTALL'GO

### Delete the data and reset the identities

\-- disable referential integrityEXECsp\_MSForEachTable'ALTER TABLE ? NOCHECK CONSTRAINT ALL'GOEXECsp\_MSForEachTable'TRUNCATE TABLE ?'GO-- enable referential integrity againEXECsp\_MSForEachTable'ALTER TABLE ? CHECK CONSTRAINT ALL'GO