Advertisements

SQL Server: Empty every table and reset their identities

I do a lot of SQL programming. More specifically, I write a lot of migration code used to convert the contents of legacy databases into newer, better-designed schemas.

I frequently found myself writing and maintaining “delete scripts” which would call delete or truncate on every table in the database. The problem with this is that every time I added a new table, I’d have to maintain the delete script. I also had to worry about dependencies, and resetting identity columns (some clients are picky).

Well today I got tired of doing it manually and came up with a single query that can empty ANY database.

-- empty all the tables
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

-- Reset Identity Columns
declare @query varchar(max)
declare @getResetQuery cursor
set @getResetQuery = CURSOR FOR
	SELECT 'DBCC CHECKIDENT([' + TABLE_NAME + '], RESEED, ' 
		+ CAST(IDENT_SEED(TABLE_NAME) AS VARCHAR(10)) + ')' QUERY
	FROM INFORMATION_SCHEMA.TABLES
	WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
		AND TABLE_TYPE = 'BASE TABLE'
open @getResetQuery
fetch next from @getResetQuery into @query
while @@FETCH_STATUS = 0
begin
	exec ( @query )
	fetch next from @getResetQuery into @query
end
close @getResetQuery
deallocate @getResetQuery

The first section uses sp_MSForEachTable to iterate over every table in the database, and delete its contents. First it disables foreign key constrains (so it doesn’t have to worry about dependencies), deletes the contents of the table, and then re-enables the constraints.

The second section resets the identity columns. Since not all tables have identity columns, we want to select only those tables which do, and then use a cursor to iterate over their names and run DBCC CHECKIDENT to reset the identity.

I tried to use sp_MSForEachTable for the second section too, but I was having trouble getting the if condition to work. If anyone can do it with sp_MSForEachTable I would love to see their code in the comments.

Good luck!

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: