Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Identify if a tables has an IDENTITY Column

    Been poking around, but how can I tell if a an identity column exists in a table?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    IDENTITY is a table property. I'm pretty sure systables has a bit flag for it...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My bad - it is in sys.columns - you need to join to sys.tables.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SQL Server 2000.

    sysobjects doesn't, and I was looking at syscolumns niether

    The schema views don't seem to have it
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Brett Kaiser
    The schema views don't seem to have it
    Schema views won't - it ain't ANSI.

    Dunno for 2k. sp_help has it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pootle flump
    Schema views won't - it ain't ANSI.

    Dunno for 2k. sp_help has it.

    Good point....time to rip it open
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here you go

    Code:
    	IF EXISTS (SELECT * from syscolumns where id = Object_ID(@TABLE_NAME) and colstat & 1 = 1)
    	  BEGIN
    		SET @sql = 'DBCC CHECKIDENT (' + @TABLE_NAME + ', RESEED, 1)'
    		EXEC(@sql)
    	  END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Whet the hell is this though?

    colstat & 1 = 1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Colstat is, I think, a bitmap. Internal use only.
    From BoL re stat:
    Quote Originally Posted by James Earl Jones
    status
    tinyint
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Brett Kaiser
    Whet the hell is this though?

    colstat & 1 = 1
    Think back to Logic 101 ... if an 8 bit register = 10101010 and you wanted to know the value of bit 1 is on or off (the low order bit) you would AND it to 0x01. If the result = 1 then it is on. If the result is 0 it is off because:
    0 AND 0 = 0
    0 AND 1 = 0
    1 AND 0 = 0
    1 AND 1 = 1

    or in hexadecimal
    Code:
     
    0000 0000 = 00   0000 0001 = 01  0000 0010 = 02  0000 0011 = 03
    0000 0100 = 04   0000 0101 = 05  0000 0110 = 06  0000 0111 = 07
    0000 1000 = 08   0000 1001 = 09  0000 1010 = 0A  0000 1011 = 0B
    0000 1100 = 0C   0000 1101 = 0D  0000 1110 = 0E  0000 1111 = 0F

    -- This is all just a Figment of my Imagination --

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In MS-SQL 2005 you could use:
    Code:
    SELECT
       t.[name] AS table_name
    ,  c.[name] AS column_name
       FROM sys.tables AS t
       JOIN sys.columns AS c
          ON (c.[object_id] = t.[object_id])
       WHERE 0 < c.[is_identity]
    -PatP

Posting Permissions

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