Vikram Kamath

August 9, 2011

SQL Server identities: identity seed, increment and current value

Filed under: SQL Server — Tags: , , , , — Vikram @ 12:39

I have been trying to get the current seed value for an identity column using SQL server object metadata. The only luck I have had so far is to get the seed value and the increment value that was used when a table with the identity was created. I thought the queries would come in handy at some time or the other in future. So, here it is.

SELECT  TABLE_SCHEMA,
	TABLE_NAME,
	IDENT_SEED(TABLE_SCHEMA+'.'+TABLE_NAME) AS IDENT_SEED_VALUE,
	IDENT_INCR(TABLE_SCHEMA+'.'+TABLE_NAME) AS IDENT_INCR_VALUE,
	IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME) AS IDENT_CURRENT_VALUE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),'TableHasIdentity') = 1
AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),'IsMSShipped') = 0
ORDER BY TABLE_SCHEMA, TABLE_NAME

Another variation of the above query also retrieves the name of the column with the identity specified.

select  sch.name as TABLE_SCHEMA,
	obj.name as TABLE_NAME,
	col.name as COLUMN_NAME,
	col.seed_value as IDENT_SEED_VALUE,
	col.increment_value as IDENT_INCR_VALUE,
	col.last_value as IDENT_CURRENT_VALUE
from sys.schemas as sch
join sys.objects as obj on obj.schema_id = sch.schema_id
join sys.identity_columns as col on col.object_id = obj.object_id
where obj.type = 'U' and obj.is_ms_shipped = 0
order by sch.name, obj.name

The SQL function IDENT_CURRENT() returns the current value or the last identity value generated for the table.

However, note that the SQL functions IDENT_SEED() and IDENT_INCR() only return the values that were used during the creation of the table. If you have reseeded the table identity since its creation, then the functions would still show the original values.

I would like to know if anyone has found a way to get the last seed value specified for any given table when it was reseeded after the table was created.

Advertisements

18 Comments »

  1. I do not know if it’s just me or if perhaps everybody else encountering problems with your site. It appears like some of the text on your content are running off the screen. Can somebody else please provide feedback and let me know if this is happening to them as well? This may be a problem with my internet browser because I’ve
    had this happen previously. Cheers

    Comment by Dining table Sets — July 3, 2013 @ 23:58

  2. My brother suggested I might like this blog. He was entirely right.
    This post actually made my day. You cann’t imagine simply how much time I had spent for this info! Thanks!

    Comment by graco replacement covers — July 3, 2013 @ 23:58

  3. Thank you, I’ve just been searching for information about this topic for a long time and yours is the greatest I’ve found out so far.
    But, what about the bottom line? Are you sure concerning the source?

    Comment by Jane — July 4, 2013 @ 01:07

  4. I like what you guys are usually up too. This type of clever work and reporting!
    Keep up the fantastic works guys I’ve added you guys to my personal blogroll.

    Comment by rainbow vacuum cleaner dealers — July 4, 2013 @ 01:19

  5. Pretty! This has been a really wonderful article.
    Many thanks for providing this information.

    Comment by simmons mattress king — July 4, 2013 @ 01:32

  6. You actually make it seem so easy with your presentation but I find this matter
    to be actually something which I think I would never understand.
    It seems too complex and extremely broad for me.
    I am looking forward for your next post, I’ll try to get the hang of it!

    Comment by website — July 4, 2013 @ 01:49

  7. Spot on with this write-up, I seriously believe this
    website needs much more attention. I’ll probably be returning to see more, thanks for the information!

    Comment by kindle direct publishing help — July 4, 2013 @ 01:54

  8. I blog often and I really thank you for your information.

    The article has truly peaked my interest. I am going
    to book mark your site and keep checking for new details about once a week.
    I subscribed to your RSS feed too.

    Comment by schwinn airdyne exercise bike — July 4, 2013 @ 01:56

  9. For newest news you have to go to see world wide web and on
    internet I found this web site as a finest website for most up-to-date updates.

    Comment by coachella 2014 — July 4, 2013 @ 02:00

  10. Pretty! This has been an incredibly wonderful
    article. Many thanks for supplying this information.

    Comment by cooking with the iron golem creeper — July 4, 2013 @ 02:01

  11. I leave a comment each time I especially enjoy a article on a
    website or I have something to contribute to the discussion.
    It’s triggered by the passion communicated in the article I looked at. And after this post SQL Server identities: identity seed, increment and current value | Vikram Kamath. I was moved enough to drop a thought 😉 I actually do have some questions for you if it’s allright.
    Could it be just me or does it look like like some of the responses look like
    left by brain dead people? 😛 And, if you are writing on other social
    sites, I would like to keep up with you. Would you make a list all of
    all your public sites like your linkedin
    profile, Facebook page or twitter feed?

    Comment by golf sets for men — July 4, 2013 @ 02:50

  12. My brother suggested I might like this website. He used to
    be entirely right. This submit actually made my day. You cann’t imagine simply how a lot time I had spent for this information! Thanks!

    Comment by golfsets.topratedbestsellers.com — July 4, 2013 @ 03:12

  13. I love your blog.. very nice colors & theme. Did you design this website yourself or did you hire someone to do it for you?
    Plz respond as I’m looking to create my own blog and would like to know where u got this from. kudos

    Comment by strollers for baby in hyderabad — July 4, 2013 @ 04:02

  14. Hi there! I could have sworn I’ve visited this site before but after looking at some of the articles I realized
    it’s neww to me. Anyways, I’m certainly happy I stumbled upon it and I’ll
    be book-marking it and checking back frequently!

    Comment by hack facebook password without downloading how to get anything — October 7, 2013 @ 06:03

  15. I have read so many posts on the topic of the blogger lovers except
    this post is genuinely a good paragraph, keep it up.

    Comment by interesting facts — November 3, 2013 @ 18:42

  16. Avoiding predatory lendung practtices iis also a big concern whsn you have
    a weak credit score. Download the free credit report request
    form from the FTC’s website. The cell will automatically return with the corresponding maturity date for a 15-day credit term and will likewise automatically input the number of Past Due Days in
    column E accordingly.

    Comment by free credit report — December 10, 2013 @ 16:51

  17. Can climate change whatever the source, affect behavior.
    It performs the complete recovery to retrieve all your inaccessible data from the hard drives.

    There is also expanded coverage of other recent news articles.

    Comment by airport extreme — April 12, 2014 @ 13:15

  18. I believe this is one of the so much significant info for
    me. And i am glad reading your article. But want to commentary on some normal things, The website style
    is perfect, the articles is in reality excellent : D.
    Excellent process, cheers

    Comment by surfing lessons in cornwall — October 2, 2014 @ 20:05


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: