Creating SQL Server Re-Runnable 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. 👍

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
George Marklow

George Marklow

98 Followers

George is a software engineer, author, blogger, and abstract artist who believes in helping others to make us happier and healthier.