2

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?

Jeff Swensen
  • 3,513
  • 28
  • 52
J Harley
  • 285
  • 3
  • 7
  • 17
  • 1
    This question may be helpful as to what to put into your where clause http://stackoverflow.com/questions/87747/how-do-you-determine-what-sql-tables-have-an-identity-column-programatically – krock Jun 08 '10 at 09:37

3 Answers3

2
select * from sys.identity_columns
Madhivanan
  • 13,470
  • 1
  • 24
  • 29
1

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.

David M
  • 71,481
  • 13
  • 158
  • 186
  • Sure but how can I query sys.columns for a specific table when the table names are not contained within sys.columns? Many Thanks, Joel – J Harley Jun 08 '10 at 09:37
1

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
devio
  • 36,858
  • 7
  • 80
  • 143