How to get a table's current identity value using SSMS?

Question

I'd like to know if there's a way to get the value of the identity for a table with SSMS.

I expected to find it in the Design view of the table, but I can't find it there.

To clarify things up:

This must be done with SSMS's UI only, not a query that has to be written. So that a non-expert can be walked through it simply.
And must give the actual value, not just the highest identity existent, so that this can verify that the identity was copied along with the schema and data from an original table copied into this database.

Why not put the query into a view or stored procedure that this "non-expert" can run? Though someone who can't be trusted to write `SELECT` queries probably shouldn't be trusted to use SSMS at all, but that's a different matter. — Michael McGriff, Dec 10 '15 at 21:20
@MichaelMcGriff You are, of course, correct. And something like that is what's being done now. But I was quite surprised that I couldn't find it in the Design view, and assumed I was missing something obvious - hence the question. — ispiro, Dec 10 '15 at 21:23
[This link](http://www.sqlservercentral.com/Forums/Topic1020340-391-1.aspx) may interest you. "It's stored internally, not in a table you can update directly (or even see)." — Michael McGriff, Dec 10 '15 at 21:30
@MichaelMcGriff Thanks. But it seems to be contradicted by the following post there (and the same in an answer below). — ispiro, Dec 10 '15 at 21:34

score 4 · Answer 1 · answered Dec 10 '15 at 20:53

4

You can do:

select IDENT_CURRENT('MyTable')

This will show you the last created identity value for your table.

answered Dec 10 '15 at 20:53

D'Arcy Rittich

Thanks. But by SSMS I meant - using SSMS's UI without `writing` a query. – ispiro Dec 10 '15 at 20:56
A query **IS** part of the UI for SSMS. – Steve Dec 11 '15 at 17:17

score 0 · Answer 2 · answered Dec 10 '15 at 21:07

0

In table design view its not possible. But one way is as follows in SSMS

Right click on table name
Click on "Edit Top 200 Rows" option. This will open top 200 records.
Now click on "Show SQL Pane" OR press "Ctrl + 3" which will show you current query
Now change that query to this way - Select top 1 id from yourTable order by id desc

answered Dec 10 '15 at 21:07

Krishnraj Rana

You don't need the first 3 steps to do the last step. You could just write that query directly, which the OP has stated they don't want to do. – Michael McGriff Dec 10 '15 at 21:12
2

Thanks. But this will only work in a simple case. In cases where the last row was removed, or when the table was copied from somewhere else without the identity - as is the case I'm mainly interested - verifying that the identity was actually copied - this will fail. – ispiro Dec 10 '15 at 21:13
Just clarifying I'm not the downvoter. (My question might not have been clear enough before I updated it, which was after you posted your answer.) – ispiro Dec 10 '15 at 21:36
This will not give you the current identity value, only the MAX for a field in a table. – Steve Dec 11 '15 at 17:20

2 Answers2