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
Published Wednesday, December 03, 2003 3:51 PM by PSteele

Comments

# re: Find all SQL Identity columns

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

Wednesday, December 03, 2003 4:05 PM by Jon Galloway

# re: Find all SQL Identity columns

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

Wednesday, December 03, 2003 4:22 PM by Patrick Steele

# Dewayne Mikkelson and his Radio WebDog, Shadow

Dewayne Mikkelson and his Radio WebDog, Shadow

Thursday, December 04, 2003 9:55 AM by TrackBack

# re: Find all SQL Identity columns

This is what I NEEDED. Thank YOU!!!!!!

Thursday, July 29, 2004 6:17 PM by Me

# re: Find all SQL Identity columns

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

Wednesday, January 31, 2007 5:02 PM by Johnny

# re: Find all SQL Identity columns

Just what I was looking for. Thanks.

Monday, February 05, 2007 2:35 PM by Joel

# re: Find all SQL Identity columns

That hit the spot right there - thanks!

Thursday, February 22, 2007 1:46 PM by Wil

# re: Find all SQL Identity columns

I'm just going to say:

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

Monday, April 02, 2007 9:49 PM by Some Other Guy