Friday, October 4, 2013

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


open dbs


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


fetch dbs into @name


while @@FETCH_STATUS = 0



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



close DBs

Deallocate dbs

No comments:

Post a Comment