sql-servert-sqlmetadataidentity-column

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

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

Solution

  • 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