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


Upload Image Close it
Select File

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

Madhivanan's TSQL Blog

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: 


Madhivanan
3 · 40% · 12898
9
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

8  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
    160 · 1% · 301
  • 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% · 896
  • 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 · 40% · 12898
  • Olga Medvedeva, Thanks for your additional information on this

    commented on Aug 23 2012 11:52PM
    Madhivanan
    3 · 40% · 12898
  • 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 · 40% · 12898
  • it is very nice subject.but i have question if there are many more tables then variable @sql can not hold more than 8000 character.and this query gives an error.please kindly suggest how to avoid this.

    commented on Jun 7 2013 11:51PM
    Jahid Ajmeri
    131 · 1% · 392
  • @Jahid,

    Look again. The variable used by Madhivanan is a VARCHAR(MAX) which will hold up to a binary 2 billion characters, which is a tidu sum more than a mere 8,000.

    If you are stuck in SQL Server 2000 or less, then it is very likely that you will have to use a loop. In this case, the loop is used as a control device and, considering the MS wrote their original code as a cursor on steroids, it would probably be OK if you used a loop for this in SQL Server 2000 or less or if you had more than 2 billion characters of code as a result of the self concatenation that Madhivanan used.

    commented on Jun 11 2013 10:28PM
    Jeff Moden
    160 · 1% · 301
  • @Jahid,

    p.s. Don't expect to be able to print out the variable and see more than 8,000 characters because SSMS doesn't like to do such a thing. Rather, use LEN() to confirm that the variable holds more than 8,000 characters.

    commented on Jun 11 2013 10:29PM
    Jeff Moden
    160 · 1% · 301

Your Comment


Sign Up or Login to post a comment.

"Resetting Identity Values for All Tables" rated 5 out of 5 by 9 readers
Resetting Identity Values for All Tables , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]