Index in Sysindexes vs Expanding indexes in Query Analyzer

Question

Post reply

Index in Sysindexes vs Expanding indexes in Query Analyzer

W R

SSC-Addicted

Points: 463
More actions
January 3, 2007 at 8:25 am

#62153

When I look for indexes in a table through SELECT name FROM sysindexes WHERE id = OBJECT_ID ('xxx') and keycnt > 0, I find indexes in results. But, when I manually go to Query Analyzer and expand table xxx and its index, there is none.

Do I need to update something so both are in sync?

Thank you.

-W R

Viewing 6 posts - 1 through 5 (of 5 total)

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

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 728159

January 3, 2007 at 11:37 am

#681006

Which indexes?

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

MohammedU SSCoach Points: 19644 More actions · Answer 2

Your query is getting everything irrespective of constrainsts (PK), indexes and statistics etc...where as QA gives you only indexes and uses the following query...

select I.name, I.status from [dbname].dbo.sysindexes I

where I.id = OBJECT_ID ('xxx') and I.indid > 0

and I.indid < 255

and INDEXPROPERTY(I.id, I.name, N'IsStatistics') = 0

and INDEXPROPERTY(I.id, I.name, N'IsHypothetical') = 0

and I.name not in (select O.name from sysobjects O where O.parent_obj = I.id

and OBJECTPROPERTY(O.id, N'isConstraint') = 1)

MohammedU
Microsoft SQL Server MVP