Post reply

Which system table maintenance the information of all identity columns?

  • August 18, 2004 at 8:47 pm

    #108972

    Which system table maintenance the information of all identity columns?

    For example:

    If you create a table test:

    CREATE TABLE test(id INT IDENTITY(10,2))

    Not allow use the funcions IDENT_SEED and IDENT_INCR, I want to get the increment value(2) and the seed value (10) of an identity column (id) by query system table.

    Thanks

  • August 19, 2004 at 6:41 am

    #519751

    The name of the table is dbo.ALLOCATION with an object id of 99. You won't find it in sysobjects but if you run SELECT OBJECT_NAME(99) you will see it really exists. This however is a protected system table and is not made access under any circumstance to you as far as I can find. It handles pretty much all ALLOCATION items such as Object ID, Identity Columns advancement, page allocation and some other items I haven't quite firgured out yet.

  • August 20, 2004 at 2:46 pm

    #520063

    Try the "syscolumns" table. The column "autoval" will show the identity seed and increment values as the 2nd and 3rd 8 digit groups from the right.

    Have Fun!
    Ronzo

  • August 20, 2004 at 3:31 pm

    #520068

    The following query will return all the table names and column names with the seed and increment values.

    SELECT left (so.name, 30),

    left (sc.name, 40),

    cast (substring(sc.autoval, 5, 1) as integer),

    cast (substring(sc.autoval, 9, 1) as integer)

    FROM syscolumns sc

    INNER JOIN

    sysobjects so

    ON sc.id = so.id

    WHERE cast (substring(sc.autoval, 5, 1) as integer) >= 1

    Have Fun!
    Ronzo

  • September 8, 2004 at 2:30 am

    #522065

    Thank Ronzo!

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

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