Listing my indexes...

Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
retrieve a list of all indexes for mytable. Under the "Name" column, I
notice several indexes that begin with "_WA" that aren't indexes I created;
I'm assuming these are SQL-internal indexes. Can someone explain what these
indexes are?
My ultimate goal is to populate a cursor with the names of my indexes and
then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
This will eventually become a scheduled job that runs weekly. However, I
don't want to be defragmented useless indexes (or those that I haven't
intentionally built).
Any pointers, insights would be appreciated.
Thanks
RozLook at example E in the DBCC SHOWCONTIG topic in Books Online.
--
Jacco Schalkwijk
SQL Server MVP
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
> retrieve a list of all indexes for mytable. Under the "Name" column, I
> notice several indexes that begin with "_WA" that aren't indexes I
> created;
> I'm assuming these are SQL-internal indexes. Can someone explain what
> these
> indexes are?
> My ultimate goal is to populate a cursor with the names of my indexes and
> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
> This will eventually become a scheduled job that runs weekly. However, I
> don't want to be defragmented useless indexes (or those that I haven't
> intentionally built).
> Any pointers, insights would be appreciated.
> Thanks
> Roz|||Hi Roz
The _WA_Sys entries in sysindexes are for column statistics, not indexes.
Please read about statistics in the Books Online, and the database option
'auto create statistics'.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
> retrieve a list of all indexes for mytable. Under the "Name" column, I
> notice several indexes that begin with "_WA" that aren't indexes I
> created;
> I'm assuming these are SQL-internal indexes. Can someone explain what
> these
> indexes are?
> My ultimate goal is to populate a cursor with the names of my indexes and
> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
> This will eventually become a scheduled job that runs weekly. However, I
> don't want to be defragmented useless indexes (or those that I haven't
> intentionally built).
> Any pointers, insights would be appreciated.
> Thanks
> Roz|||Also, why reinvent the wheel. Tara Duggan has a proc that does just that.
Here you go:
http://weblogs.sqlteam.com/tarad
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
> retrieve a list of all indexes for mytable. Under the "Name" column, I
> notice several indexes that begin with "_WA" that aren't indexes I
created;
> I'm assuming these are SQL-internal indexes. Can someone explain what
these
> indexes are?
> My ultimate goal is to populate a cursor with the names of my indexes and
> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
> This will eventually become a scheduled job that runs weekly. However, I
> don't want to be defragmented useless indexes (or those that I haven't
> intentionally built).
> Any pointers, insights would be appreciated.
> Thanks
> Roz|||Or why not pick the one which is already in Books Online (under DBCC SHOWCONTIG)?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:OH%23aPS9sEHA.1276@.TK2MSFTNGP12.phx.gbl...
> Also, why reinvent the wheel. Tara Duggan has a proc that does just that.
> Here you go:
> http://weblogs.sqlteam.com/tarad
> "Roz" <Roz@.discussions.microsoft.com> wrote in message
> news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
>> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
>> retrieve a list of all indexes for mytable. Under the "Name" column, I
>> notice several indexes that begin with "_WA" that aren't indexes I
> created;
>> I'm assuming these are SQL-internal indexes. Can someone explain what
> these
>> indexes are?
>> My ultimate goal is to populate a cursor with the names of my indexes and
>> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
>> This will eventually become a scheduled job that runs weekly. However, I
>> don't want to be defragmented useless indexes (or those that I haven't
>> intentionally built).
>> Any pointers, insights would be appreciated.
>> Thanks
>> Roz
>|||Because her procedure also does the index defrag he's looking to do. :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uDgAxQEtEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Or why not pick the one which is already in Books Online (under DBCC
SHOWCONTIG)?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:OH%23aPS9sEHA.1276@.TK2MSFTNGP12.phx.gbl...
> > Also, why reinvent the wheel. Tara Duggan has a proc that does just
that.
> > Here you go:
> >
> > http://weblogs.sqlteam.com/tarad
> >
> > "Roz" <Roz@.discussions.microsoft.com> wrote in message
> > news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
> >> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
> >> retrieve a list of all indexes for mytable. Under the "Name" column, I
> >> notice several indexes that begin with "_WA" that aren't indexes I
> > created;
> >> I'm assuming these are SQL-internal indexes. Can someone explain what
> > these
> >> indexes are?
> >>
> >> My ultimate goal is to populate a cursor with the names of my indexes
and
> >> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these
indexes.
> >> This will eventually become a scheduled job that runs weekly. However,
I
> >> don't want to be defragmented useless indexes (or those that I haven't
> >> intentionally built).
> >>
> >> Any pointers, insights would be appreciated.
> >>
> >> Thanks
> >> Roz
> >
> >
>|||This is exactly what the sample code in BOL, under DBCC SHOWCONTIG does. the sample might have been
added to one of the BOL updates, though, so make sure you all are on current BOL (latest is from Jan
2004). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:%236tbRXGtEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Because her procedure also does the index defrag he's looking to do. :)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uDgAxQEtEHA.3156@.TK2MSFTNGP12.phx.gbl...
>> Or why not pick the one which is already in Books Online (under DBCC
> SHOWCONTIG)?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
>> news:OH%23aPS9sEHA.1276@.TK2MSFTNGP12.phx.gbl...
>> > Also, why reinvent the wheel. Tara Duggan has a proc that does just
> that.
>> > Here you go:
>> >
>> > http://weblogs.sqlteam.com/tarad
>> >
>> > "Roz" <Roz@.discussions.microsoft.com> wrote in message
>> > news:57D205B1-1BB3-4176-8C69-665729CFA5C3@.microsoft.com...
>> >> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
>> >> retrieve a list of all indexes for mytable. Under the "Name" column, I
>> >> notice several indexes that begin with "_WA" that aren't indexes I
>> > created;
>> >> I'm assuming these are SQL-internal indexes. Can someone explain what
>> > these
>> >> indexes are?
>> >>
>> >> My ultimate goal is to populate a cursor with the names of my indexes
> and
>> >> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these
> indexes.
>> >> This will eventually become a scheduled job that runs weekly. However,
> I
>> >> don't want to be defragmented useless indexes (or those that I haven't
>> >> intentionally built).
>> >>
>> >> Any pointers, insights would be appreciated.
>> >>
>> >> Thanks
>> >> Roz
>> >
>> >
>>
>