title: Re-runnable SQL Server Scripts
body: <p>What are the best practices for ensuring that your SQL can be run repeatedly without receiving errors on subsequent runs?</p>
  
  <p>e.g.</p>
  
  <ul>
  <li>checking that tables don't already exist before creating them </li>
  <li>checking that columns don't already exist before creating or renaming </li>
  <li>transactions with rollback on error</li>
  <li>If you drop tables that exist before creating them anew, drop their dependencies first too, and don't forget to recreate them after</li>
  <li>Using CREATE OR ALTER PROCEDURE instead of CREATE PROCEDURE or ALTER PROCEDURE if your flavor of SQL supports it</li>
  <li>Maintain an internal versioning scheme, so the same SQL just doesn't get run twice in the first place. This way you always know where you're at by looking at the version number.</li>
  <li><p>Export the existing data to INSERT statements and completely recreate the entire DB from scratch.</p></li>
  <li><p>dropping tables before creating them (not the safest thing ever, but will work in a pinch if you know what you're doing)</p></li>
  </ul>
  
  <p>edit:
  I was looking for something like this:</p>
  
  <pre><code>IF EXISTS ( SELECT  *
              FROM    sys.objects
              WHERE   object_id = OBJECT_ID(N'[dbo].[foo]')
                      AND OBJECTPROPERTY(object_id, N'IsUserTable') = 1 ) 
  DROP TABLE foo
  </code></pre>
  
  <p>Do others use statements like this or something better?</p>
  
  <p>edit:
  I like Jhonny's suggestion:</p>
  
  <pre><code>IF OBJECT_ID('table_name') IS NOT NULL DROP TABLE table_name
  </code></pre>
  
  <p>I do this for adding columns:</p>
  
  <pre><code>IF NOT EXISTS ( SELECT  *
                  FROM    SYSCOLUMNS sc
                  WHERE   EXISTS ( SELECT id
                                   FROM   [dbo].[sysobjects]
                                   WHERE  NAME LIKE 'TableName'
                                          AND sc.id = id )
                          AND sc.name = 'ColumnName' ) 
      ALTER TABLE [dbo].[TableName] ADD [ColumnName] 
  </code></pre>