Adding Column to SQL Server using Database Defensive Programming Technique

Recently I have learned good way to add column to sql server using database defensive programming technique from my co-worker.  All the credit for this blog post goes to him.  Thank you sir incase you are reading this blog post. (I have purposefully avoid mentioning name of co-worker due to privacy reason.)

Following example is very simple and self explanatory, Incase if you didn't get anything then pass your comment in comment section.

BEGIN TRANSACTION
IF EXISTS(SELECT 1 from information_schema.tables 
          where table_name = 'MyTableName')
  BEGIN
    Print('Table Exist');

    --Add Column MyColumn
    IF NOT EXISTS(SELECT 1 from information_schema.columns 
                  where table_name = 'MyTableName' 
                  and Column_Name='MyColumn')
     BEGIN
 ALTER TABLE MyTableName ADD MyColumn varchar(345) NULL;
 Print('MyColumn Column Added');
     END

    ELSE
     
     BEGIN
 Print('MyColumn Column Already Exist');
     END



  END

Else
    BEGIN
  Print('Table does not Exist');
    END


IF @@ERROR <> 0
    BEGIN
        PRINT('Problem in running script, Rolling back');
        ROLLBACK TRANSACTION;
    END
ELSE
 BEGIN
  PRINT('Script Run Successfully');
        COMMIT TRANSACTION;
 END