Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-21 : 00:28:23
|
| Ken writes "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?These tables with the unusual [status] field values were originally built in SLQ Server 6.0, or earlier. Is it possible these are "legacy" values?Is anyone familiar with this stuff? Thanks. Ken" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-21 : 07:48:18
|
| Why not use the sp_indexes system procedure? It will give you just about every piece of information on an index without the hassle of translating the status column. Also, this is a consistent method between different SQL Server versions, and will handle any internal changes. |
 |
|
|
|
|
|
|
|
How do you determine an index is unique from sysindexes?