Geek Answers Handbook

How to find out if my stored procedure is deleted in MS SQL Server?

Sometimes it happens, how do I know when my stored procedure is deleted / deleted without my knowledge?

It annoys me every time I debug and discover that the stored procedure does not end, which was created and tested a few days ago.

Is there any way to know remote stored procedures in MS SQL Server?

+4
source share
6 answers

The standard way to check if a procedure exists

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 notifications when you drop or create a procedure:

 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 = ' dba@youraddress.com ', @subject = 'DDL Table Event', @body = @tableHTML, @body_format = 'HTML'
+7
source

If you want to be more or less independent of the database provider, you can use SQL Standard directory views called INFORMATION_SCHEMA - they are implemented in SQL Server, MySQL and many other systems:

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

If you return the string, the stored procedure is still in the system - if not, it disappeared.

Mark

+3
source 
 if not exists(select * from sysobjects where type='P' and name= @procedure_name) print 'does not exist'
+2
source

The above answers are correct, but I never understand why experienced dba do not include the schema name. SCHEMA and case-sensitive corrections are shown below.

 IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspMyProcedure' ) BEGIN print 'Does not exist' END

..

Discussion schemes on Microsoft.com

http://msdn.microsoft.com/en-us/library/dd283095%28v=sql.100%29.aspx

..

As for the best practices for if-exist ... I would also review Andy Leonard's series of articles (and increase) on Test-Driven development with TSQL.

http://www.sqlservercentral.com/articles/Testing/66553/

+1
source

Quick and dirty way:

sp_helptext process_name

If the function complains, there is no stored procedure.

If you want to learn programmatically, run the query in the sysobjects table.

0
source

If you really want to know when your procedure has been deleted, look for DDL triggers (SQL 2005 and later).

0
source

More articles:


All Articles