Madhivanan's TSQL Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

sql_server	217
t-sql	211
tsql	116
sqlserver	96
BRH	78
#SQLServer	66
#TSQL	56
SQL Server	34
function	11
SSMS	9

Archive · View All

August 2007	17
August 2010	8
June 2012	7
June 2011	7
November 2007	7
August 2012	6
May 2012	6
November 2011	6
August 2011	6
October 2011	6

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (387) · Preferences

Resetting Identity Values for All Tables

Aug 23 2012 12:00AM by Madhivanan

Pinal Dave (Blog|Twitter), Blogger,Author, Evangelist and Speaker, posted a blog post about Reseting Identity Values for All Tables. He has used the undocumented system stored procedure to perform this. As you know sp_MSForEachTable is undocumented and it is not guaranteed if it will be supported in future version.

Here is my method that uses Information_schema.columns view as base to perform this

declare @sql varchar(max)
set @sql=''
select 
	@sql=@sql+' DBCC CHECKIDENT ('+table_name+', RESEED, 1)'
from 
	information_schema.columns
where 
	columnproperty(object_id(table_name),column_name,'isidentity')=1
exec(@sql)

You may be interested to know various methods on identifying identity columns in this post Identifying identity columns using TSQL

Tags:

Previous: Exploring SSMS-Search in files
Next: Different ways to remove TIME part from DATETIME values

Madhivanan

3 · 39% · 12440

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

5 Comments

Nice job of avoiding the cursor built into the undocumented stored procedure and the stored procedure itself, ol' friend.

I do have a question, though. Why would you ever want to reseed all of the tables in a given database?

commented on Aug 23 2012 7:30PM

Jeff Moden

166 · 1% · 291

I've met with similar task to write a query to reset IDENTITY values for all tables in a database. One colleague of mine was implementing replication and he wanted that identity values in the primary database to start from 1 (as it was by default) and in the second database identity values to start from seed = 1000000. So he asked me to write similar query.

commented on Aug 23 2012 9:47PM

Olga Medvedeva

66 · 3% · 843

Jeff Moden, although this is not often done, sometimes after testing the application in production with test data, we need to delete them and reset all identity to 1. Sometimes the client themselves want to test the application with some sample data. We reset all identity values after test data are deleted although the gaps in identity does not matter in most cases.

commented on Aug 23 2012 11:47PM

Madhivanan

3 · 39% · 12440

Olga Medvedeva, Thanks for your additional information on this

commented on Aug 23 2012 11:52PM

Madhivanan

3 · 39% · 12440

Jeff Moden, by Production I actually meant the newly setup database for a new/old application. My comment is not for the existing database which is in use for several years.

commented on Aug 24 2012 1:09AM

Madhivanan

3 · 39% · 12440

Your Comment

Sign Up or Login to post a comment.

"Resetting Identity Values for All Tables" rated 5 out of 5 by 8 readers +Madhivanan