"EXISTS" Condition In SQL Server To Check Existence Of Tables And Stored Procedures In Real Time Scenario

Introduction

The "EXISTS" condition is used to check if there are any tables and stored procedures with that name.

If not, then create one, else delete this existing one and create another brand new one, by adding new features and new options like columns , column types , column type range values.

 
Description

In a real time scenario, sometimes we use two Database Servers.

• Local SERVER
• LIVE SERVER

Local server has one SQL Server database and Live Server has another SQL Server database.

 

Mostly, we create database, tables, functions, and Stored Procedures on local server. After work and successful results in Local server, we start transferring these database objects to a Live server. So again, when some changes are made in Local server database, these changes should be there on Live Server too.

 

For example - a particular table Table X has 4 columns in both live server and local server databases but for our next requirement, we changed some column type or column type range values or added new columns in Table X on Local Server database; then we should make the same changes in that Table X on Live Server. In that case, we have to use "EXISTS" condition on table and stored procedure creation and this method should be valid for other database objects.

 

Steps

Check for table name using EXISTS condition in T-SQL. 

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
   OBJECT_ID(N'[dbo].[TblExists]') AND type in (N'U'))
BEGIN
   CREATE TABLE [dbo].[TblExists](

      [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
      [CourseID] [int] NOT NULL,
      [StudentID] [int] NOT NULL,
      [Grade] [decimal](3, 2) NULL,

      CONSTRAINT [PK_TblExists] PRIMARY KEY CLUSTERED (
         [EnrollmentID] ASC
      )

      WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

   ) ON [PRIMARY]
END
GO

Here, check if that table is there or not. Then, use the below SQL query.

select * from TblExists

 
After creation of table using EXISTS condition, run the below SQL query.

select * from TblExists

 

First, check that line, then it will create new table.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
   OBJECT_ID(N'[dbo].[TblExists]') AND type in (N'U'))

Check for stored procedure name using EXISTS condition in T-SQL.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Sp_Exists')
DROP PROCEDURE Sp_Exists
go
create PROCEDURE [dbo].[Sp_Exists]
    @EnrollmentID INT
AS
BEGIN
    select * from TblExists
END
go

Here, check if that stored procedure is there or not. Then, use this SQLquery.

sp_helptext Sp_Exists

 

Here, if that stored procedure is there, drop it. Create that stored procedure in new format.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Sp_Exists')
DROP PROCEDURE Sp_Exists
go
create PROCEDURE [dbo].[Sp_Exists]
    @EnrollmentID INT
AS
BEGIN
    select * from TblExists
END
go

 After creation of stored procedure using EXISTS condition, run the below SQL query.

sp_helptext Sp_Exists

 

First, check that line. It will create a new stored procedure.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Sp_Exists')
DROP PROCEDURE Sp_Exists
go