SQL Server Performance Forum – Threads Archive

Refer to a Stored Procedure

How can I check if a stored procedure exists in a database or not. For axample, if I want to find out that sp_test exists in master or not, the following doesn’t show: IF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[master].[dbo].[sp_test]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
PRINT ‘sp_test exists!’
ELSE
PRINT ‘The SP dose not exist’ Dosen’t [master].[dbo].[sp_test] refer to the sp_test? If no, how can I point to it?
CanadaDBA

---

Are you sure it is in the master database? Just because you named it with the prefix sp doesen’t store it in master, only searches for it first in master when you call it. Anyways try this. IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘sp_test’ AND type = ‘P’) PRINT ‘sp_test exists!’
ELSE
PRINT ‘The SP dose not exist’

---

Yes, I have created the SP in master.
CanadaDBA

---

You have to specify the database name in order to search in the sysobjects for that SP. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

This is another way to do it. IF (SELECT OBJECT_ID(‘master..sp_test’)) IS NOT NULL
BEGIN
PRINT ‘sp_test exists!’
END
ELSE
BEGIN
PRINT ‘the SP does not exits!’
END MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

]]>