How do I know if my stored procedure is removed in MS SQL Server?

Question

It happens sometimes, how do i get to know when my stored procedure dropped/removed without my knowledge?

It annoys me whenever I debugs and found to know that the stored procedure doesn't exits, which created and tested some days ago.

Is there a way to know the removed Stored Procedures in MS SQL Server?

Answer 1

The standard way to check if procedure exists is

if exists(
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE routine_type = N'PROCEDURE' and routine_name = @procname)
  print 'exists'

Starting with MSSQL 2005 you can use [DDL trigger](http://msdn.microsoft.com/en-us/library/ms190989(SQL.90%29.aspx) to send email notification when procedure is dropped or created:

USE msdb
GO
CREATE TABLE ddl_log
(ID int idenity(1,1) PRIMARY KEY CLUSTERED,
 PostTime datetime,
 DB_User nvarchar(100),
 Event nvarchar(100),
 TSQL nvarchar(2000));

CREATE TRIGGER DDL_Notify
ON DATABASE
FOR DROP_PROCEDURE, CREATE_PROCEDURE
AS
DECLARE @data XML,
        @tableHTML  NVARCHAR(MAX) ;

SET @data = EVENTDATA()

INSERT msdb.dbo.ddl_log (PostTime, DB_User, Event, TSQL)
VALUES (GETDATE(), CONVERT(nvarchar(100), USER_NAME()),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

SET @tableHTML =
    N'<H1>DDL Table Event</H1>' +
    N'<table border="1">' +
    N'<tr><th>Post Time</th><th>User</th>' +
    N'<th>TSQL</th><th></tr>' +
    CAST ( ( SELECT td = PostTime, '',
                    td = DB_User, '',
                    td = TSQL, ''
              FROM msdb.dbo.ddl_log
              WHERE id = (select max(id) from msdb.dbo.ddl_log)
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Default',
    @recipients = '[email protected]',
    @subject = 'DDL Table Event',
    @body = @tableHTML,
    @body_format = 'HTML'

Answer 2

If you want to be more or less database-vendor independent, you can use the SQL Standard catalog views called INFORMATION_SCHEMA - they're implemented in SQL Server, MySQL and many other system:

select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_NAME = 'YourStoredProcName'

If you get back a row, the stored proc is still in the system - if not, it's gone.

Marc