How to 'select' the current identity value for a table?

Question

dbcc checkident (MyTable, NORESEED)

Will show the identity value in a message. I can't see the message as I need to select the identity value through a DataReader.

Anyone any ideas?

Thanks folks

score 9 · Accepted Answer · answered Oct 28 '10 at 13:07

9

SELECT IDENT_CURRENT('MyTable')

See BOL

answered Oct 28 '10 at 13:07

AdaTheDev

Excellent, I knew there had to be a way to do it. Thanks :) – Binary Worrier Oct 28 '10 at 13:24

score 5 · Answer 2 · answered Oct 28 '10 at 13:07

5

SELECT IDENT_CURRENT('TABLE_NAME')

returns NULL if there is no identity defined in the table

answered Oct 28 '10 at 13:07

il_guru

score 1 · Answer 3 · answered Oct 28 '10 at 13:07

1

 select @@identity

might work,

there is also

SELECT SCOPE_IDENTITY()

answered Oct 28 '10 at 13:07

2

I'm guess the downvote, because that returns the identity of the row you just inserted. I'm not inserting any rows, but need to know the current identity value of the table – Binary Worrier Oct 28 '10 at 13:11
2

This is not correct. @@identity can be for any table. See http://msdn.microsoft.com/en-us/library/aa933167(SQL.80).aspx. **@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. ** – Adriaan Stander Oct 28 '10 at 13:12
You don't specify the table to get the last identity from. So you can't get the identity of any table. It's always the table where you have inserted some rows just before. – Stefan Steinegger Oct 28 '10 at 13:54

3 Answers3