如何从SQL Server 2005中的indexid获取索引名

我发现一个函数 this 有助于您摆脱困境的页面:

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; 

或者更好的是:

SELECT  OBJECT_NAME(d.object_id, d.database_id) AS objectname ,
        d.index_id ,
        i.name ,
        *
FROM    sys.dm_db_index_usage_stats AS d
        LEFT OUTER JOIN sys.indexes AS i ON i.object_id = d.object_id
                                            AND i.index_id = d.index_id

我有这个解决方案,效果很好:

create table dbo.IndexNames
(database_id int not null, object_id int not null, index_id int not null, index_name sysname not null)
go

create procedure dbo.GatherIndexNames
as
begin    
declare @cur cursor
,@name varchar(128)
,@sql varchar(max)

truncate table dbo.IndexNames

set @cur = cursor for select name from sys.databases
    where database_id >= 5

open @cur
fetch next from @cur into @name
while @@fetch_status = 0
begin
    set @sql = '
    insert into dbo.IndexNames 
    (database_id, object_id, index_id, index_name)
        select db_id(''' + @name + '''),t.object_id, i.index_id, i.name
        from [' + @name + '].[sys].[tables] t
        inner join [' + @name + '].[sys].[indexes] i 
        on t.OBJECT_ID = i.object_id
        where i.index_id <> 0'
    exec (@sql)

    fetch next from @cur into @name   
end