Results 1 to 4 of 4

Thread: find identity column....

  1. #1
    Join Date
    Mar 2003
    Posts
    383

    find identity column....

    Hi:

    There is a Identity Crisis topic in SQLCentral. From it, draws a discussion on how to find all identity columns from a database. I want to share the following one which I had modified a bit from one sender(without using syscolumns directly):

    SELECT table_name, column_name, ordinal_position orgPostion, data_type
    FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    where COLUMNPROPERTY (OBJECT_ID(Table_Name),Column_Name,'IsIdentity') = 1

    -D

  2. #2
    Join Date
    Feb 2003
    Location
    Aussie Land
    Posts
    114
    thanks DLU,

    i thought there would be an easier way to find all tables with identities!!!!

    this helps me alot, as I work alot with replication.

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    I use this

    select case COLUMNPROPERTY (a.id,b.name,'IsIdentity') when 0 then 'False'
    when 1 then 'True' end as IsIdentity
    ,a.name as TableName ,b.name as COlumnName from sysobjects a, syscolumns b
    where a.type in('u','v') and
    a.id = b.id
    and a.name <> 'dtproperties' order by a.name

  4. #4
    Join Date
    Feb 2003
    Location
    Aussie Land
    Posts
    114
    thanks Mak,
    yes, that also works.

    This is what i use without using the system function:

    --Find IDENTITIES
    select
    'TableName' = o.name,
    'ColumnName' = c.name,
    *
    from sysobjects o
    inner join syscolumns c
    on o.id = c.id
    where c.status = 128
    and o.type = 'u'
    and o.status > 0
    order by o.name


    BooksOnline: SYSCOLUMNS TABLE
    cloumn: status
    data type: tinyint
    description: Bitmap used to describe a property of the column or the parameter:
    0x08 = Column allows null values.
    0x10 = ANSI padding was in effect when varchar or varbinary columns were added. Trailing blanks are preserved for varchar and trailing zeros are preserved for varbinary columns.
    0x40 = Parameter is an OUTPUT parameter.
    0x80 = Column is an identity column.


    The last sentence indicates the hex value 0x80 which is 128 in decimal value is an IDENTITY COLUMN.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •