Wednesday, January 13, 2010

Find identity columns in a database

Here's a way to find out the identity columns in a db/table

SQL 2000

SELECT
c.TABLE_NAME
,c.COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS c
WHERE COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME),c.COLUMN_NAME,'IsIdentity') = 1


SQL 2005/2008

SELECT OBJECT_NAME(Object_Id) AS Table_name,
Name AS COLUMN_NAME
FROM sys.identity_columns

for getting identity columns in table just filter on table name value