How do you determine what SQL Tables have an identity column programmatically

sql-server tsql metadata identity-column
Gabe picture Gabe · Sep 17, 2008 · Viewed 126.9k times · Source

I want to create a list of columns in SQL Server 2005 that have identity columns and their corresponding table in T-SQL.

Results would be something like:

TableName, ColumnName

Answer

DaveCrawford picture DaveCrawford · Sep 17, 2008

Another potential way to do this for SQL Server, which has less reliance on the system tables (which are subject to change, version to version) is to use the INFORMATION_SCHEMA views:

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME

Related questions

Get the unique constraint columns list (in TSQL)?

I can get a list of unique constraints fairly easily with the following query: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='UNIQUE' But how do I get a list of the columns that each unique constraint applies to?

Read More
How do you list all the indexed views in SQL Server?

How can you get a list of the views in a SQL server database that have indexes (i.e. indexed views)? I've found it's pretty easy to run an "ALTER VIEW" as I'm developing and overlook that I'm not only …

Read More