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')
精彩评论