Writing re runnable sql server scripts Part 66 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

as part 66 of sequel server tutorial in
this video we'll discuss writing re
runnable sequel scripts
please watch about 65 before proceeding
so what is a rerun Obul sequel script
every runnable sequel script is a scrap
that when run more than once will not
throw errors let's understand what we
mean by this statement with an example
let's flip to sequel server management
studio notice that here we have this
create table script this is going to
create this table TBL employee I want
this table to be created in this sample
database so that's why they're using
that use sample so this table is going
to be created within the sample database
so when I run this query at the moment
notice that I don't have this table TBL
employee so when I run this the first
time this is going to execute
successfully and the table gets created
so when we refresh this notice that we
have the table within that sample
database then if I try to run this query
once again look at what's gonna happen
we get an error why because we have the
table already that's why it says there
is already an object named TBL employee
okay so this script right here is not
rerun above so how do we make the script
free runnable pretty straightforward
check for the existence of the table if
the table doesn't exist only then try to
create the table else print a message
stating the table already exists so
obviously the next question is how do we
check for the existence of a table in a
previous session we discussed using
information and schema or tables view to
list all the tables in a sequel server
database so we're gonna make that make
use of that view
to check for the existence of the table
so look at this query select star from
information and score schemas or tables
where table name is equal to the name of
the table that we are trying to create
so if that table exists this query is
going to return a row and then we are
passing the result of this query to this
function exists so if at all if there is
anything returned by this query then
this exists function is going to return
true otherwise it's going to return
false
so basically look at this expression
right here if not exists
so if the table does not exist then go
ahead and create the table print a
message stating the table is
successfully created else print a
message stating the table already exists
pretty straightforward let's look at
this query in action and to just speed
things up I have this query already
typed so let me copy and paste that in
sequel server management studio so since
we already have the table TBL employee
when we execute this query look at this
first of all this query when we execute
this query we get a row back and we are
passing that to this exist function so
obviously you know this is going to
check this expression right here is
going to check if the table is there or
not and if the table is not there then
create the table else print this message
straight forward so let's execute this
look at that table TBL employee already
exists on the other hand if I delete
this table and execute this query notice
that table TBL employee is successfully
created refresh this TBL employee there
I execute this query once again table
TBL employee already exists no matter
how many times we execute this we don't
get an error so this script is rerun
above now to check for the existence of
the table we have used information
underscore schemas or tables there is
another way to check for the existence
of the table you can simply use this
function this sequence of a built-in
function object ID and pass the name of
the object here the name of the object
is our table name so if this object
already exists in sequel server database
then it will have an ID and this
function is simply going to return that
ID so if there is an ID for that object
then we know the table is already there
if it is null then we know you know we
don't have a table there so we'll go
ahead and create that so just to show
you that we already have the table right
here on this table TBL employee so when
we say select object ID and then pass
the name of the object which is TBL
employee
and then look at this when I execute
this query I get the ID of that object
okay
so if that is null if object ID for a
given object is null then we know for
sure that object doesn't exist we can go
ahead and create that object in this
case table and then print a message
saying table created successfully else
will print a message saying the table
already exists and just to show you that
in action let me copy this query and
paste that within sequel server
management studio the table is already
there so when we execute this table TBL
employee already exists all right now
depending on what we want to achieve
sometimes you know we may want to drop
and recreate the table so if the table
already exists we want to drop and
recreate it okay this is especially true
if we are building some test databases
okay so how do we drop and recreate the
tables you know again check for the
existence of the table and then only
drop the table because if you don't
check for the existence of the table and
then try to drop a table and if the
table doesn't exist you will get again
an error so that's why when dropping a
table check if the table already exists
and again to check for the existence of
the table you can either use information
underscore scheme or the tables view or
this object ID function okay notice that
if object ID then drop the table so if
there is if that is not null then we
know that object is there in which case
go and drop it and then recreate the
table okay
let's look at that query in action let's
copy this to sequel server management
studio so the table is already there so
when I execute this it's going to drop
the table and then recreate that okay so
no matter how many times we execute this
it's going to drop the table recreate it
drop the table recreate it okay
now you might be wondering let me
actually delete this if you don't have
this condition right here the check to
make sure that the
is there before we try to delete if we
don't have that condition and notice
that we don't have the table TBL
employee now if I try to execute this we
will get an error why because the table
doesn't exist anymore so the query
errors out right there and then this
doesn't get executed
that's why make sure you check for the
existence of the table before you you
know try to drop it let's look at
another example
now this script right here is not rerun
about look at what the script is trying
to do it's trying to add a column email
address to this table TBL employee when
we run the script the first time it's
going to run without any problem if that
column doesn't exist if I run it again
then it's going to throw an error
because a table cannot have two columns
with the same name so this script again
is not rerun above so how do we make the
script free runnable obviously check for
the existence of that column so how do
you check for the existence of the
column again you can use this
information underscore schema dot
columns we discussed about this in
previous session as well and notice that
we are checking the name of the column
not only that actually let's look at
that query in action
so when I execute the select star from
information underscore schema that
column so a column name is equal to
email address so when we execute this we
we don't we didn't get anything why
because we don't have that column there
so let me actually execute this query so
it's going to add that column to our
table TBL employee so if we expand that
we should have email address column
right there so now when we execute this
we should get it oh look at that the
column name is email address and we are
also having here table name filter and
that's very important because if there
is another table with the same column
name you know you will get that true as
well that's why we need to filter you
know on table name so if that column
email address doesn't exist on the
stable TBL employee only then try to add
that column ok and notice that here I
have another condition table schema
again this is very important especially
if your sequel server has multiple
schemas okay in multiple schemas you can
have same table name and same column
name
so obviously you don't want to be
altering a table that is present in a
different schema okay so that's why it's
very important to check the schema as
well just in case if your sequel server
has got multiple schemas if that's not
the case it's enough if you check just
the table name and the column name again
this script is rerun about no matter how
many times you run you know this is not
going to throw an error if the column
exists it's going to print that message
if it doesn't exist it's going to add
that caller again to check for the
column existence you can either use this
view or you can use this column length
function okay again just like you know
object ID function so column length you
pass the name of the table and the
column that you are looking for if that
column length function is not now then
we know that the column already exists
on
table else the column doesn't exist in
which case you can have that you know
the script to add the column to the
table all right
on this slide you can find the sources
for a speed or not c-sharp and sequels
live interview questions that's it for
today thank you for listening have a
great day