Post reply

Checking Identity property

  • June 15, 2007 at 11:10 am

    #68844

    Hi,

    How can I check if identity property has been enabled or disabled on a table? Since the identity column is NOT NULL, we are getting an error message when trying to insert new data into multiple tables (that have columns with identity property).

    Is there a quick way to check this out.

    Thanks.

    -R

  • June 15, 2007 at 11:16 am

    #713339

    To my knowledge it cannot be disabled without changing the column altogether (like to an int).

     

    What error are you getting?

  • June 15, 2007 at 12:30 pm

    #713367

    And this is how you find the Tables with Identity columns (asuming you have the rights )

    SELECT TABLE_SCHEMA, TABLE_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'ISIDENTITY') = 1


    * Noel

  • June 15, 2007 at 12:57 pm

    #713376

    CREATE TABLE dbo.UserActivity (UserActivityId INT NOT NULL IDENTITY(1,1),

    UserName VARCHAR(100), LoginTime DATETIME)

    INSERT INTO dbo.UserActivity (UserName, LoginTime)

    VALUES ('Peter', '2007-03-01 23:23:31')

    INSERT INTO dbo.UserActivity (UserName, LoginTime)

    VALUES ('John', '2007-05-6 9:33:49')

    SELECT * FROM dbo.UserActivity

    -- When you try to insert identity value into that column you get an error.

    INSERT INTO dbo.UserActivity (UserActivityId, UserName, LoginTime)

    VALUES (10, 'Ram', getDate())

    -- Server: Msg 544, Level 16, State 1, Line 1

    -- Cannot insert explicit value for identity column in table 'UserActivity' when IDENTITY_INSERT is set to OFF.

    -- Once you set the idntity_inser ON, you will be able to insert values.

    SET IDENTITY_INSERT dbo.UserActivity ON

    INSERT INTO dbo.UserActivity (UserActivityId, UserName, LoginTime)

    VALUES (10, 'Ram', getDate())

    SET IDENTITY_INSERT dbo.UserActivity OFF

    SELECT * FROM dbo.UserActivity

    Here is the question, how do I find the list of tables on which SET IDENTITY_INSERT has been turned ON?

    In the above example, if I forgot to run the command SET IDENTITY_INSERT dbo.UserActivity OFF, I want to be able to find that.

  • June 15, 2007 at 1:28 pm

    #713384

    It can be on for one and only one table at the time (not sure if it's per db or server).

     

    One quick way to check if any table is still on that option is to set a new table to ident insert on, if you get an error there, then the option is used elsewhere, if not, you can stop your search there.

  • June 15, 2007 at 1:39 pm

    #713386

    Bingo, thank you Ninja.

  • June 15, 2007 at 4:29 pm

    #713420

    Or, just set if to off... won't produce an error if it's already off.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

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