locked
How to find out the identity property is set for a table or not using T-sql RRS feed

  • Question

  • Hi Everyone,

                               how do i find whether there is any identity column in a table, and if exists which column has the identity property using T-sql and not by user interface ?

    Any help would be appreciated

    Thanks and Regards

    Chaithanya M


    Regards Chaithu.. If it is usefull,Mark this as Answer.

    Wednesday, August 22, 2012 12:44 PM

Answers

  • SELECT o.name, c.name FROM syscolumns c, sysobjects o 
    WHERE c.id = o.id AND (c.status & 128) = 128


    --------------------
    SELECT IDENT_SEED(OBJECT_NAME(id)) AS seed,
           IDENT_INCR(OBJECT_NAME(id)) AS incr,
           OBJECT_NAME(id) AS tbl
      FROM syscolumns
     WHERE (status & 128) = 128

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Wednesday, August 22, 2012 12:47 PM

All replies

  • SELECT o.name, c.name FROM syscolumns c, sysobjects o 
    WHERE c.id = o.id AND (c.status & 128) = 128


    --------------------
    SELECT IDENT_SEED(OBJECT_NAME(id)) AS seed,
           IDENT_INCR(OBJECT_NAME(id)) AS incr,
           OBJECT_NAME(id) AS tbl
      FROM syscolumns
     WHERE (status & 128) = 128

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Wednesday, August 22, 2012 12:47 PM
  • The Following query is used to list all the tables in a database with the related identity column.

    ----------------------------------------------------------------------

    SELECT   t.TABLE_NAME

            ,c.COLUMN_NAME
            ,c.TABLE_CATALOG
            ,c.TABLE_SCHEMA 
    FROM
            INFORMATION_SCHEMA.COLUMNS AS c JOIN
            INFORMATION_SCHEMA.TABLES AS t
     ON t.TABLE_NAME = c.TABLE_NAME
    WHERE
            COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME)
                  ,c.COLUMN_NAME,'IsIdentity') = 1 AND
                   t.TABLE_TYPE = 'Base Table'AND
                   t.TABLE_NAME NOTLIKE'dt%'AND
                   t.TABLE_NAME NOTLIKE'MS%'AND
                   t.TABLE_NAME NOTLIKE'syncobj_%'



    Regards, Chirag Patel (ETL Engineer @C-S-America)

    Wednesday, August 22, 2012 2:20 PM