Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    8

    Unanswered: Search for IDENTITY column

    I wanted to list all the user table & column name if it's data type is
    either an IDENTITY, INT, Smallint, Bigint or tinyInt. Here is the script:

    select b.name "Table Name", a.name "column name" ,t.name "Column Type" from syscolumns as a
    INNER JOIN sysobjects as b on a.id = b.id
    INNER JOIN sysconstraints as c on a.id = c.id
    INNER JOIN systypes as t on t.xtype= a.xtype
    WHERE b.type = 'u'
    AND (a.status = 0x80 OR t.name like '%int%')

    Somehow, it missed one of the table which has a Smallint column (as IDENTITY also). Please corrected me if I am wrong.
    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Try

    select b.name "Table Name", a.name "column name" ,t.name "Column Type"
    from syscolumns as a
    INNER JOIN sysobjects as b on a.id = b.id
    INNER JOIN systypes as t on t.xtype= a.xtype
    WHERE b.type = 'u'
    AND (a.status & 0x80=0x80 OR t.name like '%int%')

    also look at xusertype

Posting Permissions

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