A SQL-Job was failing with Could not find stored procedure ‘RemovePushMsgs’.
I noticed the drop-down-value ‘database’ was missing from the job-step – which was odd. To confirm my suspicion that the database had been removed but the job forgotton I wrote the following script to search each and every database for the missing SP.
--find_sp.sql -- to find stored-procedure names like '%RemoveM%' in all databases create table #temp (name varchar(50), dbname varchar(50), xtype varchar(50)) exec sp_Msforeachdb "use [?];insert #temp select name, '?', xtype from sysobjects where name like '%RemoveM%'" select name, dbname, case xtype when 'P' then 'SP' when 'S' then 'System Table' when 'X' then 'XP' when 'U' then 'Table' when 'TF' then 'FunctionT' when 'FN' then 'FunctionS' when 'V' then 'View' when 'IF' then 'FunctionI' when 'D' then 'Default' else xtype end [type] from #temp order by 1 drop table #temp