Writing re-runnable sql server scripts - Part 66

Suggested Videos
Part 63 - Cursors in sql server
Part 64 - Replacing cursors using joins in sql server
Part 65 - List all tables in a sql server database using a query

What is a re-runnable sql script?
A re-runnable script is a script, that, when run more than, once will not throw errors.



Let's understand writing re-runnable sql scripts with an example. To create a table tblEmployee in Sample database, we will write the following CREATE TABLE sql script.
USE [Sample]
Create table tblEmployee
(
ID int identity primary key,
Name nvarchar(100),
Gender nvarchar(10),
DateOfBirth DateTime
)



When you run this script once, the table tblEmployee gets created without any errors. If you run the script again, you will get an error - There is already an object named 'tblEmployee' in the database.

To make this script re-runnable
1. Check for the existence of the table
2. Create the table if it does not exist
3. Else print a message stating, the table already exists

Use [Sample]
If not exists (select * from information_schema.tables where table_name = 'tblEmployee')
Begin
Create table tblEmployee
(
ID int identity primary key,
Name nvarchar(100),
Gender nvarchar(10),
DateOfBirth DateTime
)
Print 'Table tblEmployee successfully created'
End
Else
Begin
Print 'Table tblEmployee already exists'
End

The above script is re-runnable, and can be run any number of times. If the table is not already created, the script will create the table, else you will get a message stating - The table already exists. You will never get a sql script error.

Sql server built-in function OBJECT_ID(), can also be used to check for the existence of the table
IF OBJECT_ID('tblEmployee') IS NULL
Begin
   -- Create Table Script
   Print 'Table tblEmployee created'
End
Else
Begin
   Print 'Table tblEmployee already exists'
End

Depending on what we are trying to achieve, sometime we may need to drop (if the table already exists) and re-create it. The sql script below, does exactly the same thing.
Use [Sample]
IF OBJECT_ID('tblEmployee') IS NOT NULL
Begin
Drop Table tblEmployee
End
Create table tblEmployee
(
ID int identity primary key,
Name nvarchar(100),
Gender nvarchar(10),
DateOfBirth DateTime
)

Let's look at another example. The following sql script adds column "EmailAddress" to table tblEmployee. This script is not re-runnable because, if the column exists we get a script error.
Use [Sample]
ALTER TABLE tblEmployee
ADD EmailAddress nvarchar(50)

To make this script re-runnable, check for the column existence
Use [Sample]
if not exists(Select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='EmailAddress' and TABLE_NAME = 'tblEmployee' and TABLE_SCHEMA='dbo') 
Begin
ALTER TABLE tblEmployee
ADD EmailAddress nvarchar(50)
End
Else
BEgin
Print 'Column EmailAddress already exists'
End

Col_length() function can also be used to check for the existence of a column
If col_length('tblEmployee','EmailAddress') is not null
Begin
Print 'Column already exists'
End
Else
Begin
Print 'Column does not exist'
End