A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Friday, October 12, 2018

Checking for the Existence of Database Objects for Idempotent Code

I am constantly having to search for the best way to determine if a certain kind of database object exists because there is no one way that works for all of the various kinds of objects. So I'm posting here all the various methods in one place, so I can easily find whichever code I need at the time. If it helps you too, all the better.

Usually when I'm looking for this kind of code when I need to make a SQL script that is repeatable any number of times without erroring out because something was already handled in a previous execution of the script. In technical parlance this is called "idempotence," the quality of producing the same result no matter how many times the script runs. The examples below apply the existence check in a bit of abbreviated code for writing idempotent code for each type of database object.

Create a Table

if not exists(select * from Information_Schema.Tables where Table_Schema = 'dbo' and Table_name = 'My_Table')
create table [dbo].[My_Table] (MyColumn int);

Update or Create a Stored Procedure:

if object_id('my_stored_procedure', 'P') is not null drop proc [dbo].[my_stored_procedure];
go
create procedure [dbo].[my_stored_procedure]
...

Add a Table Index

if not exists (select * from [sys].[indexes] as [i] inner join [sys].[tables] as [t] on [i].[object_id] = [t].[object_id] where [i].[name] = 'my_index' and [t].[name] = 'my_table])
create clustered index [my_index] on [my_table] ([my_column])

Replace clustered with whatever index type qualifiers are appropriate for the index you are creating.

Add a Column 

if col_length('[my_schema].[my_table]', 'my_column') is null
alter table [my_schema].[my_table] add [my_column] int not null

Replace int not null with whatever data type and qualifier you need for the new column.

Update or Create a Trigger

if object_id(N'[my_trigger]', 'TR') is not null
drop trigger [dbo].[my_trigger];
go

create trigger [dbo].[my_trigger] on [dbo].[my_table] 
for update
as
...

Add a Constraint

if object_id('my_constraint') is null alter table [my_table] add constraint my_constraint unique ([my_column]);

Add or Modify a User Defined Function

if object_id('my_function', 'FN') is not null drop function [dbo].[my_function];
go

create function [dbo].[my_function]
returns datatype
as
begin
 /* my code */
end

Followers