SQL Server tutorial for beginners - Writing re runnable sql server scripts Part 66

Text version of the video http://csharp-video-tutorials.blogspot.com/2013/06/writing-re-runnable-sql-server-scripts.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-66-writing-re-runnable-sql-server.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists 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