Rajeev Pentyala – Microsoft Power Platform

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.

‘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:

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.

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.

🙂