question about DMV index_usage_stats

I am still a bit new to DMV's and DMF's. How do I figure out what index_id
is referring to? Like I want to know the actual name of that index. For
instance, I can use the function database_name() to determine the actual
name of the database_id... is there like an index_name or equivilent
function?
thanksHi Adam
Unfortunately, there is no system function that will return the index name
when you pass in index_id and object_id.
You have to query the sys.indexes view directly:
SELECT name FROM sys.indexes
WHERE object_id = <supplied object id> and index_id = <supplied
index id>
or you can write your own function, something like this:
CREATE FUNCTION dbo.index_name (@.object_id int, @.index_id tinyint)
RETURNS sysname
AS
BEGIN
DECLARE @.index_name sysname
SELECT @.index_name = name FROM sys.indexes
WHERE object_id = @.object_id and index_id = @.index_id
RETURN(@.index_name)
END;
-- Sample usage:
SELECT dbo.INDEX_NAME(object_id, index_id) AS index_name, *
FROM sys.dm_db_partition_stats
WHERE object_id > 100
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Adam Clark" <aclark@.midsouthtech.net> wrote in message
news:OpKNt44VHHA.4964@.TK2MSFTNGP06.phx.gbl...
>I am still a bit new to DMV's and DMF's. How do I figure out what index_id
>is referring to? Like I want to know the actual name of that index. For
>instance, I can use the function database_name() to determine the actual
>name of the database_id... is there like an index_name or equivilent
>function?
> thanks
>