How to find all the identity fields in a database

Ever need to know all the identity fields in a database? With a current project I am working on (having to do with replication) I need to know every identity field there is in a database (and of course what tables they are in) ... so since all this information is stored in SYSCOLUMNS and SYSOBJECTS, just query them like this: 
SELECT o.name AS TableName, c.name AS ColumName
FROM syscolumns c JOIN sysobjects o 
 ON c.id = o.id
WHERE  c.status = 128 -- 0x80 is an identity column
ORDER BY o.name, c.name

posted on Monday, April 12, 2004 10:57 AM

Feedback

# re: How to find all the identity fields in a database

I use: c.colstat & 1 = 1

Here's a query I wrote a while back that gets all the column details like you see in design table mode...

SELECT c.name,
type_name(c.xusertype) AS type,
c.length,
c.isnullable,
p.value AS description,
cm.text AS defaultvalue,
c.xprec AS [precision],
c.xscale AS scale,
c.colstat & 1 AS isidentity,
CASE c.colstat & 1
WHEN 1 THEN ident_seed(t.name)
ELSE NULL
END AS identityseed,
CASE c.colstat & 1
WHEN 1 THEN ident_incr(t.name)
ELSE NULL
END AS identityincrement,
CASE c.colstat & 1
WHEN 1 THEN ColumnProperty(t.id, c.name, 'IsIDNotForRepl')
ELSE NULL
END AS identitynotforreplication,
ColumnProperty(t.id, c.name, 'IsRowGuidCol') AS isrowguid,
c.iscomputed,
f.text AS formula,
c.collation

FROM sysobjects t

INNER JOIN syscolumns c
ON c.id = t.id

LEFT JOIN sysproperties p
ON p.id = c.id
AND p.smallid = c.colid
AND p.name = 'MS_Description'

LEFT JOIN sysobjects o
ON o.parent_obj = c.id
AND o.xtype = 'D'
AND o.info = c.colid

LEFT JOIN syscomments cm
ON o.id = cm.id

LEFT JOIN syscomments f
ON c.id = f.id
AND c.colid = f.number

WHERE t.name = @TableName

ORDER BY c.colorder
4/12/2004 7:22 AM | Jim Bolla

# re: How to find all the identity fields in a database

WOW! Jim yours is really cool, brings a lot of stuff back and all very nicely put together.
4/12/2004 7:30 AM | Jason Haley

# Take Outs for 12 April 2004

Take Outs for 12 April 2004
4/12/2004 3:11 PM | Enjoy Every Sandwich

# re: How to find all the identity fields in a database

Very nice! Thanks for the script, Jim. And, thank you, Jason, for bringing up the topic.
4/13/2004 3:14 AM | David

# re: IDENTITY,ROWGUID,計算列の判別法

re: IDENTITY,ROWGUID,???????
5/2/2004 5:47 AM | たにぐちBLOG

# re: How to find all the identity fields in a database

this one is "legal", and will not use internal columns:

SELECT name = c.name
FROM sysobjects o, syscolumns c
WHERE COLUMNPROPERTY(o.id, c.name, 'IsIdentity')= 1 AND o.id = c.id
3/10/2005 7:44 PM | EH

# re: How to find all the identity fields in a database

Nice script!!
It was useful.

Thks!
1/22/2007 12:52 PM | Guille

# re: How to find all the identity fields in a database

Jim's script worked great. I could able to identify the columns that are 'Identity' easily in the database. - Thanks Jim.
2/7/2007 3:13 PM | Diwakar Mahanti

# re: How to find all the identity fields in a database

thanx
6/22/2007 4:02 AM | Sagar

# re: How to find all the identity fields in a database

Very Very HelpFUl and Guiding
6/21/2008 8:15 AM | Brijesh

Post Comment

Title  
Name  
Url
Comment   
Please enter the following code into the box below to stop spammers

   		Enter Code Here *