CODEWITHSUNDEEP
sqlsql-server
J Harley
J Harley

Reputation: 285

Finding a Identity Specification using SQL

I have a SQL Server database, of which there is a column that has an identity specification.

However, if I do a SQL query such as: 

SELECT * FROM INFORMATION_SCHEMA.Columns where TABLE_NAME =

It doesn't tell me if the column is an identity specification - is there a query that will?

Upvotes: 2

Views: 2684

Answers (3)

devio
devio

Reputation: 37215

Using the sys.columns system catalog view:

select o.name, c.name, c.is_identity
from sys.objects o
inner join sys.columns c on o.object_id = c.object_id
where o.type='U'
--and o.name='MyTable'
and c.is_identity = 1

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

select * from sys.identity_columns

Upvotes: 2

David M
David M

Reputation: 72870

Unlikely to be in the INFORMATION_SCHEMA views, which are ANSI defined, as identity is a SQL Server-specific feature. You could use the SQL Server-specific tables or views (depending on your version of SQL Server) like syscolumns / sys.columns.

Upvotes: 1

Related Questions