Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: How do you determine an index is unique from sysindexes?

    I need to write a script which will respond differently if a table's index forces uniqueness.

    I am using SQL 7.0 and I am using the [sysindexes] table of my database to examine those indexes. I am using the [id] field from the [sysobjects] table for the table I am examining and I am using that [id] field to find like-valued [id] fields in the [sysindexes] table.

    My problem is that I can't seem to determine, from an examination of the [sysindexes] table, whether, or not, an index is unique.

    When adding an index, and checking the unique property of that index, the values that I get in the [status] field of [sysindexes] are different from the values which are supposed to show, at least according to the sysindexes (T-SQL) entry in SQL Server Books Online.

    For instance, I have a table which combines two text fields which comprise the only key on a table. The unique and clustered boxes are checked in the index setup screen. When I look in the [sysindexes] table, the value in the [status] field is 2113554, which is not a value I see in the books online page. According to my books online page, a unique index should have a value of 2 in the [status] field and a clustered index should have a value of 16 in the [status] field. My assumption is that I should see a value of 18 in that [status] field, not 2113554.

    I looks like the books online entry might be out-of-date because the field that is labeled [reserved1] in my books online page, is labeled [StatVersion] in my actual [sysindexes] table. That [StatVersion] field looks suspiciously like a Status Version field, possibly indicating that the Status field has undergone some sort of version revision?

    Is anyone familiar with this stuff?

    Thanks.

    Ken

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Nevermind.

    Just read the scripting from sp_help index. It's got all the answers I was looking for.

    Thanks.

    Ken

Posting Permissions

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