How to get the Index Name from IndexId in SQL Server 2005

Question

I'm running the following query to list size and fragmentation of the indexes:

SELECT object_name(object_id, database_id) as objectname), index_id, *
FROM sys.dm_db_index_usage_stats

Is there an SQL function I can use to convert the index_id to the index name?

Answer 1

I found a function on this page that should help you out:

CREATE FUNCTION dbo.index_name (@object_id int, @index_id int)
RETURNS sysname
AS
BEGIN
  RETURN(SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id)
END;