Script to determine identity table and identity column

Written by

Jugal Shah

in

, ,

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

Comments

5 responses to “Script to determine identity table and identity column”

  1. Moods Avatar
    Moods

    Good one

    Reply
  2. nikhil Avatar
    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 Avatar
      Jugal Shah

      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 Avatar
    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 Avatar
    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

More posts