May 10, 2007 at 4:33 am
Hi guys
Can I possibly list all the nonclustered indexes in a database at once and how can I achieve this? your kind response if any is much appreciated.
May 10, 2007 at 8:38 am
This should help. You can also look at sp_helpindex in master to see how it does similar work to undertsand other options.
select object_name([id]) [Object_Name], indid, groupid, name, (case when (status & 16)<>0 then 'clustered' else 'nonclustered' end) type, status from sysindexes
where indid > 0 and indid < 255 and (status & 64)=0 order by indid
May 11, 2007 at 2:05 am
Just some additional information of the sysindex table:
indid column (ID of index):
1 = Clustered index. (equivalent to (status & 16) = 0)
>1 = Nonclustered.
255 = Entry for tables that have text or image
data.
status column (Internal system-status information):
1 = Cancel command if attempt to insert duplicate key.
2 = Unique index.
4 = Cancel command if attempt to insert duplicate row.
16 = Clustered index.
64 = Index allows duplicate rows.
2048 = Index used to enforce PRIMARY KEY constraint.
4096 = Index used to enforce UNIQUE constraint.
Do not forget, not all entries in the sysindex table are true indexes. For example entries with name starting with _WA_Sys_ are statistics created automatically by the system
Bye
Gabor
May 11, 2007 at 7:39 am
Since this is SQL there is always another way to do something.
select name
from sysobjects
where objectproperty(id,'TableHasNonclustIndex') = 1
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply