Translate index_id to the name of the index

The system function sys.dm_db_index_physical_stats returns the fragmentation of all indexes on a server. Instead of names, it returns the id of the tables and indexes. The names of databases, schema's and objects can be found using db_name, object_schema_name, and object_name:

select  db_name(ips.database_id)
,       object_schema_name(ips.object_id, ips.database_id)
,       object_name(ips.object_id, ips.database_id)
,       ips.index_id
,       ips.avg_fragmentation_in_percent
from    sys.dm_db_index_physical_s开发者_运维技巧tats(null, null, null, null, null) ips

The function object_name does not work for indexes. A join to sys.indexes works, but it's database specific, so you have to run it in a cursor with dynamic SQL.

Is there a better way to translate index_id to the name of the index?

Instead of using sys.dm_db_index_physical_stats without the database parameter set, why not loop through a list of databases from sys.databases pulled into a temp table and then querying for the index stats with the specific databases. There's an extensive script posted here on this topic.

the system view dm_db_index_physical_stats contains a column object_id. This is the id of the object(table) in the sysobjects table. So you can simply look it up thus:

SELECT name FROM sys.sysobjects WHERE id = 'add object id here'

name will be the table name. Obviously this could be also be turned into an INNER JOIN to prevent the additional call.