Check if stored proc exists in DB?


I am trying to execute scripts of procedures in another database. The problem is this stored procedure exist or not in database. For handling this problem, I wrote following script which checks if stored proc exists in database and if does then provide execute for user?

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')

Popular posts from this blog

SQL Basic- SQL Syntax

Image
          SQL Syntax:            SQL Syntax Database Tables A database contains one or more tables. Each table is identified by a name (e.g. "Student" or "Product").  Tables contain records (rows) with data. Below is a selection from the " Student " table: StudentID             StudentName               Address                Country 1             Ajay               Noida                India 2             Ajeet               Bareilly                India 3             Shivam               Nagaland...
Read more

Type Declaration Instruction in C

Type Declaration Instruction This instruction is used to declare the type of variables being used in the program. Any variable used in the program must be declared before using it in any statement. The type declaration statement is written at the beginning of main( ) function. Ex.: int bas ; float rs, grosssal ; char name, code ; There are several subtle variations of the type declaration instruction. These are discussed below: While declaring the type of variable we can also initialize it as shown below. int i = 10, j = 25 ; float a = 1.5, b = 1.99 + 2.4 * 1.44 ; The order in which we define the variables is sometimes important sometimes not. For example, int i = 10, j = 25 ; is same as int j = 25, j = 10 ; However, float a = 1.5, b = a + 3.1 ; is alright, but float b = a + 3.1, a = 1.5 ;         is not. This is because here we are trying to use a even before defining it. The following statements would work int a, b, c, d ; a = b = ...
Read more

SQL Basic- SQL Commands

Image
          SQL Commands                       The SQL commands to interact with relational databases are: CREATE,  SELECT,  INSERT,  UPDATE,  DELETE and  DROP.               These commands can be classified into groups based on their nature:           DDL - Data Definition Language.            DML - Data Manipulation Language.            DCL - Data Control Language.
Read more