List of Identity Columns in Database

  • Hi,

     

    How to get list of identity columns in database.

    Can any one help me?

     

    Regards

    Satish

  • Have a look in BOL for OBJECTPROPERTY and COLUMNPROPERTY

    /Kenneth

  • select  * from syscolumns where autoval is not null

    will list the columns, but you need to know what you are doing if you want to add tablenames, etc

  • This came up yesterday in another forum.

     

     

    Simple example from BOL:

    SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname','IsIdentity')

     

    A query on the Information Schema Views for all identity columns:

     

    SELECT

         OBJECT_ID(col.Table_Name)  AS [Object_ID]

       , COLUMNPROPERTY(

                OBJECT_ID(col.Table_Name)

              , col.Column_Name,'IsIdentity'

              ) AS [IsIdentity]

       , *

    FROM

         Information_Schema.Columns col

    WHERE

         COLUMNPROPERTY(

              OBJECT_ID(col.Table_Name)

            , col.Column_Name,'IsIdentity') = 1

     

    Good luck,

    Joshua

  • Thank you for sending the response. I am getting the list of columns. And i am trying to get the Seed value and increments values for the columns.

     

    Regards

    Satish

  • these can be obtained from IDENT_INCR and IDENT_SEED

    SELECT

     Table_Name,

     Column_Name,

     IDENT_SEED(Table_Name) Seed,

     IDENT_INCR(Table_Name) Incr

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE

    COLUMNPROPERTY (OBJECT_ID(Table_Name),Column_Name,'IsIdentity') = 1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply