What's a more recent stored procedure, Development or Prod?

select

do.name, do.create_date, do.modify_date, po.create_date, po.modify_date


from Development.sys.objects do join Production.sys.objects po on do.name = po.name

where

do.type = 'P'

and

do.name not like 'asp%'

and

abs(datediff (hh, do.modify_date, po.modify_date)) > 2

and

do.modify_date > po.modify_date

order

by do.name

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