A quick guide how to write safe SQL scripts

This is quite trivial but I am going to provide few common examples how to ensure safe multiple run of sql server scripts. Of course we have to check if the script has already been executed to avoid errors or duplication.

SQL TABLE
Check if a table exists before its creation:

IF (NOT EXISTS
(SELECT * FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA = N'SchemaName'
          AND  TABLE_NAME = N'TableName'))
BEGIN
    --Run table creation script
END

SQL TABLE COLUMN
Check if a column exists before adding it:

IF NOT EXISTS(
    SELECT * FROM sys.columns WHERE Name = N'ColumnName'
    AND Object_ID = Object_ID(N'TableName'))
BEGIN
    --Run column definition script
END

SQL VIEW
Check if a view exists and remove it in order to safely create the one:

IF EXISTS(select * FROM sys.views where name = N'ViewName')
BEGIN
   DROP VIEW ViewName
END  

go 

CREATE VIEW ViewName ....

SQL STORED PROCEDURE
Check if a stored procedure exists and remove it

IF EXISTS (SELECT * FROM sys.objects
           WHERE object_id = OBJECT_ID(N'ProcName')
           AND type IN ( N'P', N'PC' ) )
BEGIN
   DROP PROCEDURE dbo.[ProcName]
END
go
CREATE PROCEDURE ...

SQL FUNCTION
Check if a function exists and remove it

IF EXISTS (SELECT * FROM sys.objects
           WHERE object_id = OBJECT_ID(N'[dbo].[FuncName]')
           AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
BEGIN
  DROP FUNCTION [dbo].[FuncName]
END
go
CREATE FUNCTION...

SQL TABLE INDEX
Check if an index exists and remove it

IF EXISTS (SELECT * FROM sys.indexes
           WHERE name='IndexName'
           AND object_id = OBJECT_ID('TableName'))
BEGIN
   --DROP INDEX ...
END

SQL DATA ENTRY
Check whether data has already been updated.

IF NOT EXISTS (SELECT * FROM SampleTable WHERE )
BEGIN
  INSERT INTO SampleTable...
END

IF EXISTS (SELECT * FROM SampleTable WHERE )
BEGIN
  UPDATE SampleTable SET .... WHERE
END