status = 0 in Sysindexes

Question

Post reply

status = 0 in Sysindexes

dkhattar

SSCrazy

Points: 2037
More actions
August 3, 2007 at 5:29 am

#62916

Hi,
I have few indexes for a table which have status = 0 in sysindexes, what that means?
Deepak

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the status column is used a s a bitfield mask to query whether an index is clustered,unique, etc.

the status of zero is going to mean false for all indexproperties; it might be an index that was autocreated.

you should use the indexproprty to find out the info about an index, but here's an example for do-it-yourselfers like me: the results of the proc give you something like this, and the code of the proc can be adapted if you need to look at something more in depth

name	name	index description	index column 1	index column 2	index column 3
ACACTDET	PK__ACACTDET__49C658BC	clustered, unique, primary key	ACACTDETTBLKEY	NULL	NULL
GMHOPWA1	UQACTAREA	nonclustered, unique, unique key	ACTTBLKEY	ACTAREATBLKEY	NULL

CREATE procedure sp_help_db_indexes

AS

declare @empty varchar(1)

select @empty = ''

-- 35 is the lenght of the name field of the master.dbo.spt_values table

declare @IgnoreDuplicateKeys varchar(35),

@Unique varchar(35),

@IgnoreDuplicateRows varchar(35),

@Clustered varchar(35),

@Hypotethical varchar(35),

@Statistics varchar(35),

@primarykey-2 varchar(35),

@UniqueKey varchar(35),

@AutoCreate varchar(35),

@StatsNoRecompute varchar(35)

select @IgnoreDuplicateKeys = name from master.dbo.spt_values

where type = 'I' and number = 1 --ignore duplicate keys

select @Unique = name from master.dbo.spt_values

where type = 'I' and number = 2 --unique

select @IgnoreDuplicateRows = name from master.dbo.spt_values

where type = 'I' and number = 4 --ignore duplicate rows

select @Clustered = name from master.dbo.spt_values

where type = 'I' and number = 16 --clustered

select @Hypotethical = name from master.dbo.spt_values

where type = 'I' and number = 32 --hypotethical

select @Statistics = name from master.dbo.spt_values

where type = 'I' and number = 64 --statistics

select @primarykey-2 = name from master.dbo.spt_values

where type = 'I' and number = 2048 --primary key

select @UniqueKey = name from master.dbo.spt_values

where type = 'I' and number = 4096 --unique key

select @AutoCreate = name from master.dbo.spt_values

where type = 'I' and number = 8388608 --auto create

select @StatsNoRecompute = name from master.dbo.spt_values

where type = 'I' and number = 16777216 --stats no recompute

select o.name,

i.name,

'index description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on

case when (i.status & 16)<>0 then @Clustered else 'non'+@Clustered end

+ case when (i.status & 1)<>0 then ', '+@IgnoreDuplicateKeys else @empty end

+ case when (i.status & 2)<>0 then ', '+@Unique else @empty end

+ case when (i.status & 4)<>0 then ', '+@IgnoreDuplicateRows else @empty end

+ case when (i.status & 64)<>0 then ', '+@Statistics else

case when (i.status & 32)<>0 then ', '+@Hypotethical else @empty end end

+ case when (i.status & 2048)<>0 then ', '+@PrimaryKey else @empty end

+ case when (i.status & 4096)<>0 then ', '+@UniqueKey else @empty end

+ case when (i.status & 8388608)<>0 then ', '+@AutoCreate else @empty end

+ case when (i.status & 16777216)<>0 then ', '+@StatsNoRecompute else @empty end),

'index column 1' = index_col(o.name,indid, 1),

'index column 2' = index_col(o.name,indid, 2),

'index column 3' = index_col(o.name,indid, 3)

from sysindexes i, sysobjects o

where i.id = o.id and

indid > 0 and indid < 255 --all the clustered (=1), non clusterd (>1 and <251), and text or image (=255)

and o.type = 'U' --user table

--ignore the indexes for the autostat

and (i.status & 64) = 0 --index with duplicates

and (i.status & 8388608) = 0 --auto created index

and (i.status & 16777216)= 0 --stats no recompute

order by o.name

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

#1CoolGuy Ten Centuries Points: 1189 More actions · Answer 2

You can retrieve the meanings of the different values in the sysindexes status column by running the following

Select * from master.dbo.spt_values where type = 'I'

The same information can also be retrieved about an index via the IndexProperty function.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Ummm... Not quite true unless you know how to use IndexProperty to identify if it's a primary key or not... If you know a way to do that, I'm all ears...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bob Fazio SSChampion Points: 10794 More actions · Answer 4

SELECT

OBJECT_NAME(OBJECT_ID),type_desc FROM sys.indexes

INNER

JOIN sysindexes

ON

sys.indexes.object_id = sysindexes.id

AND

sys.indexes.index_id = sysindexes.indid

WHERE

sysindexes.status=0

status = 0 means that your table is a heap table (no clustered index).

philcart SSC-Forever Points: 47794 More actions · Answer 5

Huh ... silly me ... I usually just run sp_helpindex ...

--------------------
Colt 45 - the original point and click interface

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6