"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. 
  1. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =   
  2.    OBJECT_ID(N'[dbo].[TblExists]'AND type in (N'U'))  
  3. BEGIN  
  4.    CREATE TABLE [dbo].[TblExists](  
  5.   
  6.       [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,  
  7.       [CourseID] [intNOT NULL,  
  8.       [StudentID] [intNOT NULL,  
  9.       [Grade] [decimal](3, 2) NULL,  
  10.   
  11.       CONSTRAINT [PK_TblExists] PRIMARY KEY CLUSTERED (  
  12.          [EnrollmentID] ASC  
  13.       )  
  14.   
  15.       WITH (IGNORE_DUP_KEY = OFFON [PRIMARY]  
  16.   
  17.    ) ON [PRIMARY]  
  18. END  
  19. GO 
Here, check if that table is there or not. Then, use the below SQL query.
  1. select * from TblExists 
 
After creation of table using EXISTS condition, run the below SQL query.
  1. select * from TblExists 
 
First, check that line, then it will create new table.
  1. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =   
  2.    OBJECT_ID(N'[dbo].[TblExists]'AND type in (N'U')) 
Check for stored procedure name using EXISTS condition in T-SQL.
  1. IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Sp_Exists')  
  2. DROP PROCEDURE Sp_Exists  
  3. go  
  4. create PROCEDURE [dbo].[Sp_Exists]  
  5.     @EnrollmentID INT  
  6. AS  
  7. BEGIN  
  8.     select * from TblExists  
  9. END  
  10. go 
Here, check if that stored procedure is there or not. Then, use this SQLquery.
  1. sp_helptext Sp_Exists 
 
Here, if that stored procedure is there, drop it. Create that stored procedure in new format.
  1. IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Sp_Exists')  
  2. DROP PROCEDURE Sp_Exists  
  3. go  
  4. create PROCEDURE [dbo].[Sp_Exists]  
  5.     @EnrollmentID INT  
  6. AS  
  7. BEGIN  
  8.     select * from TblExists  
  9. END  
  10. go 
 After creation of stored procedure using EXISTS condition, run the below SQL query.
  1. sp_helptext Sp_Exists 
 
First, check that line. It will create a new stored procedure.
  1. IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Sp_Exists')  
  2. DROP PROCEDURE Sp_Exists  
  3. go   
Ebook Download
View all
Learn
View all