CODEWITHSUNDEEP
sql-serveridentity
user3569267
user3569267

Reputation: 1125

find the column identity in a table in sql server

Can someone know if there is a way to find the column name that has the identity property in an sql server table

Thanks in advance

Upvotes: 1

Views: 65

Answers (3)

Matt Gibson
Matt Gibson

Reputation: 38238

As an alternative to looking it up in the metadata, you can use $identity to refer to an identity column. For example, given a table:

CREATE TABLE test_table (some_random_name INT IDENTITY, other_column VARCHAR(20));

...you can use:

SELECT $identity, other_column FROM test_table;

to bring back some_random_name, other_column.

($identity replaces the deprecated IDENTITYCOL of earlier versions of SQL Server.)

Upvotes: 2

Behrooz
Behrooz

Reputation: 1955

You can use the solution in this link to search the whole database for a specific value, which can be a username you know exists "somewhere" in the database. It gives you the table and column name where that specific value exists.

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

You can use sys.columns for this quite easily.

select * 
from sys.columns
where object_id = object_id('YourTableNameHere')
    AND is_identity = 1

Upvotes: 4

Related Questions