Find an index across databases

Some tools report back the name of an index without the name fo the database it's in... other folks may have an index in some of their SaaS databases, but not all. Here's one way to find them all.

 

 

declare dbs cursor for

select name from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb') order by name

go

open dbs

 

declare @name varchar(1000), @string varchar(2000)

 

fetch dbs into @name

 

while @@FETCH_STATUS = 0

begin

 

fetch dbs into @name

set @string = 'select * from [' + @name +'].sys.indexes where name like ''_dta%'''

set @string = 'if exists ( ' + @string + ') begin ' + 'select '''+ @name + '''' + @string + ' end'

 

print @string

 

end

 

close DBs

 

Deallocate dbs