January 3, 2007 at 8:25 am
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
January 3, 2007 at 11:37 am
Which indexes?
January 3, 2007 at 12:46 pm
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
January 3, 2007 at 2:59 pm
Thanks. On the money. How do I read more about this. Any pointers?
-W R
January 3, 2007 at 6:16 pm
BOL is the best place to start...
MohammedU
Microsoft SQL Server MVP
January 4, 2007 at 6:06 am
sp_helpindex tablename will list index details for a table
sp_autostats tablename will list the names of stats, system stats and indexes
The inside sql xxx books contain good information on indexes.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply