During development, it’s nice to reset the database tables and load the database up to a known state for testing purposes without having to reload the schema.
Well, I was running into a problem where calling DBCC CHECKIDENT() on a newly built database would actually reseed the identity column to the value passed into CHECKIDENT(). For example, after creating a new database (without any previous INSERTs), and calling “DBCC CHECKIDENT(‘table’, reseed, 0)”, the very next INSERT on ‘table’ would cause the identity column on the newly inserted row to be 0 on ‘table’ when it should be 1.
Now, if you had existing rows in ‘table’, deleted the existing rows, then reseeded using “DBCC CHECKIDENT(‘table’, reseed, 0)”, the very next INSERT on ‘table’ would cause the identity column on the newly inserted row to be 1 (as expected).
Whack. So, calling DBCC on a new database (without any previous inserts) next INSERT identity would be 0, and calling DBCC on a existing database (with previous inserts) next INSERT identity would be 1. This kind of inconsistent behavior totally defeated my DB tests. Fortunately, there’s a way to differentiate between newly created tables (with no prior rows) and tables that had have had rows even after a delete. Here’s a script that I made that completely resets tables and identity columns apart of your build process:(!BE VERY CAREFUL!)
Yes it’s a bit hacky, using undocumented stored procedures and system tables, but it’s the best I could do. MS doesn’t make this easy…
WARNING THIS WILL COMPLETELY DELETE ANY DATA IN YOUR DATABASE
SET NOCOUNT ON Exec sp_MSForEachTable 'Alter Table ? NoCheck Constraint All' Exec sp_MSForEachTable ' If ObjectProperty(Object_ID(''?''), ''TableHasForeignRef'')=1 Begin -- Just to know what all table used delete syntax. Print ''Delete from '' + ''?'' Delete From ? END ELSE BEGIN -- Just to know what all table used Truncate syntax. Print ''Truncate Table '' + ''?'' Truncate Table ? END ' Exec sp_MSForEachTable ' DECLARE @lastIdentVal sql_variant SELECT @lastIdentVal = LAST_VALUE FROM SYS.IDENTITY_COLUMNS WHERE OBJECT_ID=Object_ID(''?'') If ObjectProperty(Object_ID(''?''), ''TableHasIdentity'')=1 AND @lastIdentVal NOT NULL BEGIN DBCC CHECKIDENT (''?'', reseed, 0) DBCC CHECKIDENT (''?'', reseed) END ' Exec sp_MSForEachTable 'Alter Table ? Check Constraint All'
Hope that helps,
Share onTwitter Facebook Google+ LinkedIn
Leave a comment
Your email address will not be published. Required fields are marked *