How do I determine the table from index name

I ran a query to identify indexes with fragmentation problems, and it gives
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?
What query/mechanism are you using to generate the list of fragmented
indexes?
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||First, you might be re-inventing the wheel. You will find code in Books Online which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
database id as second parameter (very useful).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query that
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:

> First, you might be re-inventing the wheel. You will find code in Books Online which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>
|||Probably a service broker queue. Perhaps you are using SB explicitly or for the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...[vbcol=seagreen]
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since that
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query that
> shows that it is actually the name of the table or view - which I just said
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>