Post reply

how can i find out if a table has an identity column programatically?

  • April 28, 2009 at 2:53 pm

    #130303

    I need to issue different query based on if the table has an identity column or not. Since I am working with serveral tables, I need to find it at run time programatically. Is there a catalog view or information schema view where I can find out if a table has identity column or not?

  • April 28, 2009 at 3:01 pm

    #985510

    Check COLUMNPROPERTY in Books Online. You can use this function to determine is a column is an identity column.

  • April 28, 2009 at 3:09 pm

    #985515

    You can also use the OBJECTPROPERTY function to see if a table has an identity. Check OBJECTPROPERTY in Books Online

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 28, 2009 at 3:15 pm

    #985522

    This is what I came up with..

    if (select max(cast(is_identity as int)) from sys.columns where object_id = object_id(@tname))=1

    then identity column exists

    else identity column dont exists..

    is there a better way?

  • April 28, 2009 at 3:19 pm

    #985525

    if exists (select 1 from sys.columns c where c.object_id = object_id(@tname) and c.is_identity =1)

    begin

    -- identity column exists

    end

    else

    begin

    -- identity column does not exists..

    end

    But if you give us what you are planning to do with this we may offer a better answer 😉


    * Noel

  • April 28, 2009 at 3:19 pm

    #985526

    Thanks gail..

    select objectproperty(object_id(N),'TableHasIdentity')

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

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