DBCC SHOWCONTIG

I've been running above procedure using the "with all_indexes, fast;" option
on a table in my database. This table has 18 indexes. The results in this
procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
indexes all have english names (e.g. Account, LastName, etc.). I'm not sure
which index ID is referring to what named index. Are these ID numbers simply
the order in which they appear when I see the list in Enterprise Manager,
Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG procedure
reflect the actual index names I had assigned?
Thanks, Jim
Hi Jim
What version are you using? In SQL 2005 there is a replacement for DBCC
SHOWCONTIG that allows you to filter the results and add more information to
the output. The output is much more readable, too. DBCC is not a procedure,
so there is very little you can do to modify how it works.
You cannot assume the index numbers are the same as the order the indexes
come back in the EM list. You can run this query to see what number goes
with what index: (substitute your own table name, of course)
SELECT indid, name
FROM sysindexes
WHERE id = object_id('your-table-name')
AND indexproperty(id,name, 'isStatistics') = 0
AND indexproperty(id,name, 'isHypothetical') = 0
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
> I've been running above procedure using the "with all_indexes, fast;"
> option
> on a table in my database. This table has 18 indexes. The results in
> this
> procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
> indexes all have english names (e.g. Account, LastName, etc.). I'm not
> sure
> which index ID is referring to what named index. Are these ID numbers
> simply
> the order in which they appear when I see the list in Enterprise Manager,
> Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG
> procedure
> reflect the actual index names I had assigned?
> --
> Thanks, Jim
|||Kalen. Thanks for the quick reply. I have to to support sites that use
either SQL2000 or SQL2005. What would be the better option to DBCC
SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
great in both 2000 and 2005. Thanks again for help.
Thanks, Jim
"Kalen Delaney" wrote:

> Hi Jim
> What version are you using? In SQL 2005 there is a replacement for DBCC
> SHOWCONTIG that allows you to filter the results and add more information to
> the output. The output is much more readable, too. DBCC is not a procedure,
> so there is very little you can do to modify how it works.
> You cannot assume the index numbers are the same as the order the indexes
> come back in the EM list. You can run this query to see what number goes
> with what index: (substitute your own table name, of course)
> SELECT indid, name
> FROM sysindexes
> WHERE id = object_id('your-table-name')
> AND indexproperty(id,name, 'isStatistics') = 0
> AND indexproperty(id,name, 'isHypothetical') = 0
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim B" <JB@.lightning.com> wrote in message
> news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
>
>
|||Jim
In SQL 2005 there is a table valued function called
sys.dm_db_index_physical_stats. It returns a LOT of information, but you can
filter it in any way you like, and since it returns table results, you can
join it to the sys.indexes to get the index name, and you can restrict the
columns coming back to just what you need.
You should read about it in BOL first, and if you have a SQL Server Magazine
subscription, you can find a couple of articles I wrote about it on their
site.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1E20DDE1-6435-4ED1-A4F4-E7402C3B05B2@.microsoft.com...[vbcol=seagreen]
> Kalen. Thanks for the quick reply. I have to to support sites that use
> either SQL2000 or SQL2005. What would be the better option to DBCC
> SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
> great in both 2000 and 2005. Thanks again for help.
> --
> Thanks, Jim
>
> "Kalen Delaney" wrote: