SQL Server Performance

How to find the "identity column name" in a table?

Discussion in 'Getting Started' started by Janani Priya, Apr 24, 2008.

  1. Janani Priya New Member

    hi all....
    if the table has an identity column......i need a query to get only the identity column name from a given table........
    is it possible using information schema/sysobjects/syscolumns or not????????
    if any body know the query let me know as soon as possible.......
    thanks in advance
    regards
    priya

  2. FrankKalis Moderator

    One way would be something like this:
    SELECT
    TABLE_NAME, COLUMN_NAME
    FROM
    information_schema.columns
    WHERE
    COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1



  3. Janani Priya New Member

    thank u for ur response Mr.Frank.........
    ya its giving me the expected output.......
    Is there any way to get the record of all columns from the table except the identity column record in a single query.........
    is it possible?????????
    thanks & regards
    priya
  4. FrankKalis Moderator

    [quote user="Janani Priya"]
    thank u for ur response Mr.Frank.........
    ya its giving me the expected output.......
    Is there any way to get the record of all columns from the table except the identity column record in a single query.........
    is it possible?????????
    thanks & regards
    priya
    [/quote]
    Sure. Just negate the WHERE filter by changing the 1 to 0.
  5. Adriaan New Member

    I knew it was buried somewhere in BOL -
    In SQL 2000: SELECT IDENTITYCOL FROM ....
    In SQL 2005: SELECT $IDENTITY FROM ...
    The keyword is resolved by SQL Server, and the results from the column are returned, under the correct column name.
    Of course if you have to retrieve all columns except the identity column, then Frank's approach is the right one.
  6. Madhivanan Moderator

    I think OP wants to do
    Select * -- exclude identity column
    from table
  7. jagblue New Member

    Hi All
    SELECT * SYS.COLUMNS
    WHERE COLUMN_ID NOT IN (SELECT COLUMN_ID FROM SYS.IDENTITY_COLUMNS)
    AND OBJECT_NAME(OBJECT_ID) = 'tblname'
    Will give you desire result
    Thanks

Share This Page