Delete Table Data & Reset Identities in SQL


When you are designing databases, transformation scripts and getting ready to go live with them you often need the ability to “clean out” your database. Maybe you have tested the transformation script that pulls your data from the old database, “transforms” it and puts it in the new database and now all that data needs to be removed.

The problem arises when you have relationships between these tables. SQL will not allow you to simply truncate the tables when there are foreign key restraints. Normally you would have to remove the restraints, remove the data and then recreate the restraints. However, this is a pain. You can DELETE the data, but then you’re left with identity values that are not 1.

I recently posted an article on the MSDN blogs about this here. This came up with a good idea, which was embarassing that I hadn’t remembered. You can simply have SQL Server Management Studio generate the script for your database. This was a good option since it’s quick and doesn’t require a lot of work.

However, I ran into a problem. I needed the supporting tables in the database to retain their values and only the primary tables get “reset”. So, I decided to design this script that will do it for me. Yes, it makes use of a cursor, but since this is something that’s not used on a regular basis I think the performaince issues will be minimal.

Declare @tblName varchar(255)
Declare c1 CURSOR READ_ONLY
For Select s.TABLE_NAME
From INFORMATION_SCHEMA.TABLES s
Where Left(s.TABLE_NAME,3)
Not In ('lst','sys')
Order By s.TABLE_CATALOG,
s.TABLE_NAME
Open c1
Fetch Next
From c1
Into @tblName

While @@FETCH_STATUS = 0
BEGIN

Declare @sql nvarchar(4000)
If (@tblName Is Not Null And LTrim(RTrim(@tblName)) != '')
Begin
Set @sql = 'Delete ' + @tblName
-- Print @sql
Exec sp_sqlexec @sql

Set @sql = 'DBCC CHECKIDENT(''' + @tblName + ''', RESEED, 0) ;'
-- Print @sql
Exec sp_sqlexec @sql
End
Else
Begin
Print 'Table ''' + @tblName + ''' not found!'
End

Fetch Next
From c1
Into @tblName
END
Close c1
Deallocate c1

* Please pardon the formatting. WordPress is causing the CODE blocks to be opened and closed around each line or two and is removing my spacing and tabbing.

Of course, you will have to run this multiple times due to primary key restraints needing dependent records removed before those they depend on. If you have the time you can modify the select statement in the cursor to perform these tasks in the correct order so you only have to execute it once. I didn’t have the time for this, so I just executed it a few times until I saw no more errors.

I hope this helps! If it did, please take time to comment and let me know.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s