Find all SQL Identity columns

I had a need today to find all columns in a SQL database that were identity columns (across all tables). Thanks to SQL's system tables, it was a breeze:

select o.name + '.' + c.name 
from syscolumns c, sysobjects o
where c.status & 128 = 128
and o.id = c.id
order by o.name

6 Comments

  • Patrick -



    I think you're safer using the INFORMATION_SCHEMA views, since the system tables are subject to change. Same query run against INFORMATION_SCHEMA:



    select table_name + '.' + column_name, table_name, column_name, ordinal_position, data_type

    from information_schema.columns

    where

    table_schema = 'dbo'

    and columnproperty(object_id(table_name), column_name,'IsIdentity') = 1

    order by table_name



    - Jon

  • Thanks Jon! For those running on a case-sensitive server, here's an update to Jon's query:



    select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME

    from INFORMATION_SCHEMA.COLUMNS

    where TABLE_SCHEMA = 'dbo'

    and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

    order by TABLE_NAME

  • Thanks for the solution.

    If you are using SQL 2005 and Schemas, you'll need to specify the table_schema value when calling the object_id function:

    select table_schema + '.' + table_name + '.' + column_name, table_name, column_name, ordinal_position, data_type
    from information_schema.columns
    where columnproperty(object_id(table_schema + '.' + table_name), column_name,'IsIdentity') = 1
    order by table_name

  • Just what I was looking for. Thanks.

  • That hit the spot right there - thanks!


  • I'm just going to say:

    This is just what they were looking for. On their behalf, I give you many thanks.

Comments have been disabled for this content.