Check IF Exist For Trigger , Function And Stored Procedure

Introduction
In this article we will learn how to check if exist condition for Stored Procedure , Function and Trigger in sql server . Why do we need to check if exists. How to use If Exists in SQL.

Previous Updates
In previous articles we have learnt Const Readonly and Static difference in C#, SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT difference .What is Cursor in sql and use of cursor.  Best basic C# interview questions.


Whenever you are working with SQL server you always need to prepare Scripts of Stored Procedures , triggers , Functions , Indexes etc. If you are working only for yourself then it is OK, but if you work as an company or Company employee then you have to prepare Scripts for Client Machine or other's machine using IF Exists statement. Without this your generated script will fail.

Check If Exist For Stored Procedure

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =  OBJECT_ID(N'[Schema].[Procedure_Name]') AND type IN (N'P', N'PC')) BEGIN DROP PROCEDURE [Schema].[Procedure_Name] Print('Proceudre dropped => [Schema].[Procedure_Name]') END GO

You can also add Your Create Query in Else Block also. Just like 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =  OBJECT_ID(N'[Schema].[Procedure_Name]') AND type IN (N'P', N'PC')) BEGIN DROP PROCEDURE [Schema].[Procedure_Name] Print('Proceudre dropped => [Schema].[Procedure_Name]') END ELSE BEGIN --  Your Create Procedure Query Here Print('Proceudre Created => [Schema].[Procedure_Name]') END GO

Check If Exist For Trigger

--------------------------Drop Trigger---------------------------- IF EXISTS (SELECT * FROM sys.objects WHERE [name] =             N'[Trigger_Name]' AND [type] = 'TR') BEGIN DROP TRIGGER [Trigger_Name] Print('Trigger dropped => [Schema].[Trigger_Name]') END GO

Check If Exist For Function

--------------------------Drop Function--------------------------- IF  EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE              object_id = OBJECT_ID(N'[Schema].[function_Name]')              AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN  DROP FUNCTION [Schema].[function_Name]  Print('function dropped => [Schema].[function_Name]') END GO