Script to determine identity table and identity column

5 Replies

Script to determine the identity tables and columns in the databases.

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”

  2. nikhil

    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 ↓
    1. Jugal Shah Post author

      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. contact united cash loans

    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. Mayra

    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 ↓

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s