USE <DBNAME>
--script to determine the table with the identity column
select name, OBJECTPROPERTY(id, 'TableHasIdentity') AS TableHasIdentityCol
from sysobjects
where xtype = 'U'

--script to determine the table and columne with the identity on
SELECT OBJECT_NAME(id) as TblName, name as ColName
FROM syscolumns
WHERE status = 0x80

--script to determine the table and columne with the identity on using ColumnProperty
SELECT OBJECT_NAME(id) as TblName, name as ColName
FROM syscolumns
WHERE COLUMNPROPERTY(id, name, 'IsIdentity') = 1

5 thoughts on “Script to determine identity table and identity column”

1. Good one

   Reply
2. I have a query here. If we are going to set identity for a column in a table. do we have exact sql script for this without any backup or by modifying this table.i searched a lot but no success. Looking forward for your response

   Reply
   • Can you give more information or example what you are trying to achieve??? you can script out the table if you want to check the table creation script…

     Reply
3. Thanks for a marvelous posting! I quite enjoyed reading
   it, you could be a great author. I will make certain to bookmark your blog and definitely
   will come back at some point. I want to encourage you continue your great work,
   have a nice day!

   Reply
4. Hello There. I found your blog using msn. This is an extremely well written article.

   I will make sure to bookmark it and come back to read more of your useful info.
   Thanks for the post. I will definitely comeback.

   Reply