SQL Server Alert on Missing Stored Procedure

Question

I’m cleaning out a large database and am going to be removing a lot of stored procedures. I can be pretty certain but not 100% that the SP isn’t being used. How can I be alerted that something is calling a missing stored procedure, and throwing the error:

Could not find stored procedure ‘X’

Answer 1

You could create synonyms for each deleted procedure and substitute a different procedure (with matching parameters). In the example below the procedure doesn't take any parameters.

The code:

1. create "replacement" proc called 'dbo.test_replacement_proc'
2. exec 'dbo.test_replacement_proc'
3. create "test" proc called 'dbo.test_proc'
4. exec 'dbo.test_proc'
5. drop 'dbo.test_proc'
6. create synonym 'dbo.test_proc' for 'dbo.test_replacement_proc'
7. exec 'dbo.test_proc'

When #4 executes it's the original procedure. When #7 executes it's the replacement procedure.

drop proc if exists dbo.test_replacement_proc;
go
create proc dbo.test_replacement_proc
as
set nocount on;
select 'Could not find stored procedure' replacement_message
go

-- run the proc
exec dbo.test_replacement_proc;

-- Create proc
drop synonym if exists dbo.test_proc;
drop proc if exists dbo.test_proc;
go
create proc dbo.test_proc
as
set nocount on;
select 'This is the text' procedure_message
go

-- run the proc
exec dbo.test_proc;

-- drop procedure
drop proc if exists dbo.test_proc;

-- Create a synonym for the deleted procedure
create synonym dbo.test_proc
for dbo.test_replacement_proc;  
go  

-- run the proc (which is now a synonym for the replacement proc)
exec dbo.test_proc;

Output #2

replacement_message
Could not find stored procedure

Output #4

procedure_message
This is the text

output #7

replacement_message
Could not find stored procedure