Trying to find indexes only in sysindexes table

Question

Trying to find indexes only in sysindexes table

ducksql@gmail.com

SSCrazy

Points: 2826
More actions
October 18, 2011 at 9:49 am

#258889

I have been tasked with creating a stored procedure to defrag all indexes for our ERP system. I have found a table called sysindexes that lists all indexes for each table; however, it also lists all statistics for each table.
I have two questions. First, how can I differentiate between indexes and statistics in this table? Second, can I defrag the statistics as well as the indexes?
Thank you.
Steve

Viewing 13 posts - 1 through 12 (of 12 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Are you on SQL 2000?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

statistics can be identified like this, i believe:

select

OBJECT_NAME(id),*

from sysindexes

where first is null --NULL for statsitics

and indid >0 --avoid HEAP indexes

they typically have a name that starts with _WA_Sys_ as well.

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!

ducksql@gmail.com SSCrazy Points: 2826 More actions · Answer 3

GilaMonster: Yes, this is SQL 2000. Sorry for not clarifying.

Lowell: When I run this code, I get no records.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

If you do a DBCC DBREINDEX it will defrag all the indexes on the table.. Why are you trying to go index by index?

CEWII

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Assuming SQL 2K.

INDEXPROPERTY has pretty much all the info you want but I don't have a fully build script for ya.

Gail wants to know if you mistakenly posted in the wrong forum so she can suggest this instead for sql 2K5+ => http://sqlfool.com/2011/06/index-defrag-script-v4-1/

Lowell SSC Guru Points: 323518 More actions · Answer 6

i ran mine on SQL 2005/2008; the views may report different results between versions, i guess.

how about ....WHERE name like '_WA_Sys_%'

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Lowell's code is one way, you can also use the status column. Status is 8388672 for statistics and other values for indexes. Also filter for indid>0

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Lowell (10/18/2011)
i ran mine on SQL 2005/2008; the views may report different results between versions, i guess.
how about ....WHERE name like '_WA_Sys_%'

That will only catch the auto-created stats, not any that may have been manually created.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ducksql@gmail.com SSCrazy Points: 2826 More actions · Answer 9

Elliott: We want to use DBCC INDEXDEFRAG because we need to be able to do this online and keep the tables available for the users. It also allows us to cancel the job before it is finished if necessary. DBCC REINDEX does not afford us these luxuries.

GilaMonster: Thank you very much. That is exactly what I was looking for. The numbers must be slightly different between databases because my magic number is 8388704, but your answer allowed me to find it. This will save me a LOT of time.

Thanks all of you for your help.

Steve

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

sdownen05 (10/18/2011)
GilaMonster: Thank you very much. That is exactly what I was looking for. The numbers must be slightly different between databases because my magic number is 8388704, but your answer allowed me to find it. This will save me a LOT of time.

I'd suggest rather use the IndexProperty function. I forgot it existed, but there's a property 'IsStatistics' that you can use to find the items that aren't statistics

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

GilaMonster (10/18/2011)
sdownen05 (10/18/2011)
GilaMonster: Thank you very much. That is exactly what I was looking for. The numbers must be slightly different between databases because my magic number is 8388704, but your answer allowed me to find it. This will save me a LOT of time.
I'd suggest rather use the IndexProperty function. I forgot it existed, but there's a property 'IsStatistics' that you can use to find the items that aren't statistics

+21

where status = q32423047132940832 doesn't say much ;-).

There can be a little, very negligible, performance hit by using indexproperty especially multiple times to get the list of objects to work on but I think it's worth paying it!

ducksql@gmail.com SSCrazy Points: 2826 More actions · Answer 12

Thanks again. I will look into all of this and incorporate what I can.