How to check stored procedure is exist or not

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to check stored procedure is exist or not

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-11-25 : 23:09:28

Hi,

I need a tips, how to check A stored procedure is exist or not. Is that have special query?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-25 : 23:16:42

This is the code that Management Studio generates

IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[procname]') 
    AND type in (N'P', N'PC'))
BEGIN
    --Do something
END

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-25 : 23:32:15

if(object_id(<proc name>) is not null)
select 'object exists'
else
select 'no object exists'

--------------------------------------------------
S.Ahamed

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-26 : 00:18:25

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[SP_Name]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
PRINT 'SP Exists'
END

====================================================
you realize you've made a mistake, take immediate steps to correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 00:41:11

[code]SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='your prodecure name'
AND ROUTINE_TYPE='PROCEDURE'[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources