Creating SQL Re-Runnable Database Scripts

A Reference to Prevent Hiccups in SQL Server Scripts

Photo by Sid Suratia on Unsplash

Contents

• Introduction
• Table
• Column
• Constraint
• Stored Procedure
• View

Introduction

A re-runnable — or idempotent — SQL script means that no matter how many times you run it, you always get the same outcome. This means, for example, that you won’t get an error when you attempt to add the same table or drop a column that no longer exists.

This article will show you how to put checks in place to ensure you’re re-runnable SQL scripts are executed without hiccups.

n.b. Indexes are coming shortly!

Data

You can use the EXISTS operator to find out if a record exists.

IF EXISTS (SELECT 1 FROM Person WHERE Name = 'George Marklow')
BEGIN
    [...]
END
GO

n.b. SELECT 1 is an inexpensive way to check if a record matches your WHERE clause.

You can also combine this with and an ELSE statement to take action if a record does not exist:

IF EXISTS (SELECT 1 FROM Person WHERE Name = 'George Marklow')
BEGIN
    [...]
ELSE
BEGIN
    [...]
END
GO

Or, if you’re checking if data does not exist, place the NOT operator in front of EXISTS:

IF NOT EXISTS (SELECT 1 FROM Person WHERE Name = 'George Marklow')
BEGIN
    [...]
END
GO

Table

Tables store data in a relational database. You query INFORMATION_SCHEMA.TABLES to check for the existence of a table by:

• TABLE_SCHEMA : table schema
• TABLE_NAME : table name

Create

IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'dbo' 
        AND TABLE_NAME = 'Person')
BEGIN
    CREATE TABLE [dbo].[Person] (
        Id INT PRIMARY KEY,
        Name VARCHAR NOT NULL,
        Age INT NOT NULL)
END
GO

Drop

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'dbo' 
        AND TABLE_NAME = 'Person')
BEGIN
    DROP TABLE [dbo].[Person]
END
GO

Column

A database table consists of one or more columns, with each column referring to a specific type of data that you want to store in the database.

You can check for the existence of a column using INFORMATION_SCHEMA.COLUMNS , specifying:

• TABLE_SCHEMA : table schema
• TABLE_NAME : table name
• COLUMN_NAME : column name

Add

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo' 
        AND TABLE_NAME = 'Person'
        AND COLUMN_NAME = 'Address')
BEGIN
    ALTER TABLE [dbo].[Person] 
    ADD Address VARCHAR NOT NULL
END
GO

Drop

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.columns
    WHERE TABLE_SCHEMA = 'dbo' 
        AND TABLE_NAME = 'Person' 
        AND COLUMN_Name = 'Address')
BEGIN
    ALTER TABLE [dbo].[Person] DROP COLUMN Address
END
GO

Constraint

SQL constraints are used to define the rules that govern the data in a table, restricting the types of data that SQL Server may enter into a table column.

Below checks for the existence of any constraint, but examples further down show you how to check for specific constraints:

• Unique
• Check
• Primary Key
• Foreign Key

You need to query the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table, specifying:

• TABLE_SCHEMA : table schema
• TABLE_NAME : table name
• CONSTRAINT_NAME : constraint name

Add

The following example checks for the existence of a constraint called FK_Person_Order, a foreign key between two tables:

IF NOT EXISTS (SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = 'dbo'
        AND TABLE_NAME = 'Person'
        AND CONSTRAINT_NAME = 'FK_Person_Order')
BEGIN
    ALTER TABLE [dbo].[Person]
    ADD CONSTRAINT FK_Person_Order
END
GO

Drop

IF EXISTS (SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = 'dbo'
        AND TABLE_NAME = 'Person'
        AND CONSTRAINT_NAME = 'FK_Person_Order')
BEGIN
    ALTER TABLE [dbo].[Person]
    DROP CONSTRAINT FK_Person_Order
END
GO

Unique Constraint

Unique constraints prevent duplicate data from being entered into a table column.

IF NOT EXISTS (SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = 'dbo'
        AND TABLE_NAME = 'Person'
        AND CONSTRAINT_NAME = 'UQ_Person_Email'
        AND CONSTRAINT_TYPE = 'UNIQUE')
BEGIN
    ALTER TABLE Person
    ADD CONSTRAINT UQ_Person_Email UNIQUE (Email)
END
GO

Check Constraint

The types of data that a user can enter into the database are limited by check restrictions.

IF NOT EXISTS (SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = 'dbo'
        AND TABLE_NAME = 'Person'
        AND CONSTRAINT_NAME = 'CK_Person_Age'
        AND CONSTRAINT_TYPE = 'CHECK')
BEGIN
    ADD CONSTRAINT CK_Person_Age 
    CHECK (Age => 18)
END
GO

Primary Key Constraint

Each record in a database table is uniquely identified by the primary key restriction, meaning duplicate and NULL values are not allowed. Each table can only have one primary key.

IF NOT EXISTS (SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = 'dbo'
        AND TABLE_NAME = 'Person'
        AND CONSTRAINT_NAME = 'PK_Person_Id'
        AND CONSTRAINT_TYPE = 'PRIMARY KEY')
BEGIN
    ALTER TABLE Person 
    ADD CONSTRAINT PK_Person_Id 
    PRIMARY KEY(Id)
END
GO

Foreign Key Constraint

A foreign key constraint connects data in one table with the primary key in a different table, maintaining the accuracy and consistency of data through referential integrity.

IF NOT EXISTS (SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = 'dbo'
        AND TABLE_NAME = 'Person'
        AND CONSTRAINT_NAME = 'FK_Person_Order'
        AND CONSTRAINT_TYPE = 'FOREIGN KEY')
BEGIN
    ALTER TABLE Person
    ADD CONSTRAINT FK_Person_Order
    FOREIGN KEY (Id) REFERENCES Order(Id);
END
GO

Stored Procedure

A stored procedure is a stored SQL declaration in a database, helpful if you’re running the same query repeatedly.

Create

From SQL Server 2016 (13.x) SP1 onwards, you can create or alter a stored procedure without the need for IF NOT EXISTS :

CREATE OR ALTER PROCEDURE GetPeopleByCity @City nvarchar(30)
AS
    SELECT * FROM People WHERE City = @City
GO

Otherwise, you’ll need to check for the existence of a stored procedure by querying sysobjects. This is a system catalog view to find all objects in a SQL database.

You need to check:

• TYPE: a stored procedure has type P
• NAME: the name of the stored procedure

IF NOT EXISTS(SELECT 1 FROM sysobjects 
    WHERE TYPE = 'P'
        AND NAME = 'GetPeopleByCity')
BEGIN
    CREATE PROCEDURE GetPeopleByCity @City nvarchar(30)
    AS
    SELECT * FROM People WHERE City = @City
END
GO

Rename

Alternatively, you might need to check if a stored procedure exists before attempting to rename it:

IF EXISTS(SELECT 1 FROM sysobjects 
    WHERE TYPE = 'P' 
        AND NAME = 'GetPeopleByCity')
BEGIN
    EXEC sp_rename 'GetPeopleByCity', 'GetPeopleByCity_v2'
END
GO

n.b. using sp_rename to change the name of a stored procedure (and triggers, user-defined functions, or views) can break scripts, and it’s recommended to drop the procedure instead first, then re-create it.

Drop

Since SQL Server 2016, you can now do the following to drop a stored procedure:

DROP PROCEDURE IF EXISTS [dbo].[GetPeopleByCity]

Alternatively:

IF EXISTS(SELECT 1 FROM sysobjects 
    WHERE TYPE = 'P' 
        AND NAME = 'GetPeopleByCity')
BEGIN
    DROP PROCEDURE [dbo].[GetPeopleByCity]
END
GO

View

A view is a virtual table that optimizes the database experience. Composed of separate columns from one or more tables, a view is stored in a database as a query object that serves the following purposes:

• Simplication of query execution, e.g., encapsulating the logic for complex query joins.
• Returning a subset of data contained in a table.
• Securing the underlying tables from a read and/or write perspective.

The critical difference between a stored procedure and a view is that a stored procedure can have both input and out parameters to perform a function, e.g., inserting, updating, or returning single values from data sets. Stored procedures can also control data flow, e.g., using IF and ELSE keywords.

Create

SQL Server 2016 (13.x) SP1 and onwards provides the CREATE VIEW with OR ALTER argument. This allows us to get around checking for the existence of a view as if it already exists, it is simply updated:

CREATE OR ALTER VIEW [dbo].[vw_Person] (Name, Age) 
AS ( 
    SELECT Name, Age
    FROM Person 
)
GO

Otherwise, you can use the old syntax:

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS
    WHERE
    TABLE_NAME = 'vw_Person'
    and TABLE_SCHEMA = 'dbo')
BEGIN
    CREATE VIEW [dbo].[vw_Person] (Name, Age) 
    AS ( 
        SELECT Name, Age
        FROM Person 
    )
END
GO

Drop

DROP VIEW IF EXISTS vw_Person; 
GO

Thanks for reading! Let me know what you think in the comments section below, and don’t forget to subscribe. 👍