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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s