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 nullalter 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
...
go
create function [dbo].[my_function]
returns datatype
as
begin
/* my code */
end
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
