Advertisements
Home > SQL > SQL Server- Drop If Exists (DIE) Objects

SQL Server- Drop If Exists (DIE) Objects

Other day, I generated my Database SQL Script including script to create Tables, Procedures, Types, Views etc., using Tasks -> Generate Scripts… option which I had to deploy on my test instance.

DIE_1

‘Generate Scripts’ generate the SQL script with ‘CREATE’ statement (i.e., It will have ‘CREATE Table’, ‘CREATE PROCEDURE’ etc…)

Issue is, what if I already had Database script ran previously and want to only update the Database objects, you would get script error if you run the generated SQL Script.

Solution is, we need to check existence of the object (i.e.,Table, Procedure, View etc.) and Drop if exists the object and run the CREATE statement generated using ‘Generate Scripts…’ option.

Drop If Exists (DIE) statement in SQL Server 2016:

From SQL Server 2016 CTP3 we can use DIE  statements to check if the Object exists and Drop before run the CREATE statements.

  • Dropping ‘Procedure’ if exists:

DROP PROCEDURE IF EXISTS [Stored_Procedure_Name]

  • Dropping ‘Table’ if exists:

DROP TABLE IF EXISTS [Table_Name]

  • Dropping ‘Type’ (i.e.,User Defined Table Type/User Defined Data Types) if exists:

DIE_2

DROP TYPE IF EXISTS [Type_Name]

  • Delete Table’s COLUMN if Exists.

ALTER TABLE DROP COLUMN IF EXISTS

  • Delete Table’s CONSTRAINT if exists

ALTER TABLE DROP CONSTRAINT IF EXISTS

Below are the list of Objects that can be included in DIE statements.

DIE_3

Note:

  • If your SQL version is before 2016 below is the syntax

IF OBJECT_ID(‘Table_Name‘, ‘U’) IS NOT NULL
DROP TABLE Table_Name;

  • You can also use ‘Check for object existence‘ and ‘Script DROP and CREATE‘ options while ‘Generating Script’ which delete and recreate objects.

DIE_4.PNG

🙂

Advertisements
Categories: SQL Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: