SQL Server Objects - IF EXISTS

When you have the same software on different servers and not one of them has the same version numbers and you need to get all the SQL Databases aligned, you will have your work cut out. One of the practises I use when developing on a SQL Database is to instead of Alter the database objects, I use the If Exists check first, and if the object exists, I drop it before I create it (The DROP and CREATE method).



An example:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Name]') AND Type IN (N'U'))
BEGIN
 DROP TABLE [dbo].[tbl_Name]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_Name](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [CreateOn] [int] NULL,
 [ConnectTo] [int] NULL,
 [Name] [nvarchar](255) NULL,
 CONSTRAINT [PK_tbl_Name] PRIMARY KEY CLUSTERED
(
 [SYN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

The reason I use the DROP and CREATE method is because you can run the scripts over and over and it will not have an impact on the final result. Your SQL Script will also not break if you run the SQL Query for a second time and you try to create the object again if it already exists. This is also handy when you do data manipulation, it will not insert the record again if it already exists.

I've compiled a list of IF NOT EXISTS check per SQL Object:

SQL View:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vw_Name]') AND type IN (N'V'))

Stored Procedure:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Name]') AND type IN (N'P', N'PC'))

SQL Table:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Name]') AND Type IN (N'U'))

SQL Table Column:

IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'COL_Name' AND Object_ID = Object_ID(N'tbl_Name'))

SQL Table Valued Function:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fx_Name]') AND type IN (N'TF',N'IF'))

Scalar Valued Function:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fx_Name]') AND Type IN (N'FN'))

SQL Trigger:

IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tr_Name]')  AND Type IN (N'TR'))

Wednesday, July 27, 2016

Columns , EXISTS , Scalar Valued Functions , SQL , Table Valued Functions , Tables , Triggers , Views