开发者

Check if stored proc exists in DB?

i am trying to grant execute privs in stored proc in more than one databas开发者_StackOverflowe. The problem is this stored proc might not be in some of the databases. So how can i write a script which checks if stored proc exists in database and if does then provide execute privs for user?


many ways to do it:

1)

IF EXISTS (SELECT name 
       FROM   sysobjects 
       WHERE  name = N'proc1' 
       AND    type = 'P')

2)

IF EXISTS (SELECT * 
           FROM   information_schema.routines
           WHERE  routine_name = 'Proc1')


Try this:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[your_procedure_name]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
  -- Set privileges here
END


Try this:

if exists (select 1
      from sysobjects
      where  id = object_id('YourProc')
      and type = 'P')
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜