Re-runnable SQL Server Scripts

Question

What are the best practices for ensuring that your SQL can be run repeatedly without receiving errors on subsequent runs?

e.g.

• checking that tables don't already exist before creating them
• checking that columns don't already exist before creating or renaming
• transactions with rollback on error
• If you drop tables that exist before creating them anew, drop their dependencies first too, and don't forget to recreate them after
• Using CREATE OR ALTER PROCEDURE instead of CREATE PROCEDURE or ALTER PROCEDURE if your flavor of SQL supports it
• 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.

• Export the existing data to INSERT statements and completely recreate the entire DB from scratch.

• dropping tables before creating them (not the safest thing ever, but will work in a pinch if you know what you're doing)

edit: I was looking for something like this:

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[foo]')
                    AND OBJECTPROPERTY(object_id, N'IsUserTable') = 1 ) 
DROP TABLE foo

Do others use statements like this or something better?

edit: I like Jhonny's suggestion:

IF OBJECT_ID('table_name') IS NOT NULL DROP TABLE table_name

I do this for adding columns:

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]

Answer 1

For maintaining schemas, look at a migration tool. I think LiquiBase would work for SQL Server.

Answer 2

To add to your list:

• If you drop tables that exist before creating them anew, drop their dependencies first too, and don't forget to recreate them after
• Using CREATE OR ALTER PROCEDURE instead of CREATE PROCEDURE or ALTER PROCEDURE if your flavor of SQL supports it

But ultimately, I would go with one of the following:

• 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.
• Export the existing data to INSERT statements and completely recreate the entire DB from scratch.

Answer 3

You'll also need to check for foreign keys on any tables that you may be dropping/recreating. Also, consider any data changes that you might make - delete rows before trying to insert a second time, etc.

You also might want to put in code to check for data before deleting tables as a safeguard so that you don't drop tables that are already being used.

Answer 4

I think the most important practice in ensuring that your scripts are re-runnable is to....run them against a test database multiple times after any changes to the script. The errors you encounter should shape your practices.

EDIT

In response to your edit on syntax, in general I think it is best to avoid the system tables in favor of the system views e.g.

if exists(Select 1 from information_schema.tables where table_name = 'sometable')
    drop sometable
go
if exists(Select 1 from information_schema.routines where 
specific_name  = 'someproc')
   drop someproc

Answer 5

For a SQL batch statement, you can issue

This is just a FYI, I just ran it 10 times

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[foo]')
                    AND OBJECTPROPERTY(object_id, N'IsUserTable') = 1 ) 
DROP TABLE foo


GO 10 -- run the batch 10 times

This is just a FYI, I just ran it 10 times

Beginning execution loop Batch

execution completed 10 times.

Answer 6

I recently found a check-in for existence that i didn't know existed and i liked it because it's shorter

IF OBJECT_ID('table_name') IS NOT NULL DROP TABLE table_name

before, i used to use

IF EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'table_name') 
DROP TABLE table_name

Which i found useful because it's a little more portable (MySql, Postgres, etc), taking into account the differences, of course

Answer 7

To make things easier I configure management studio to script objects as rerunnable

1. Tools
2. Options
3. SQL Server Object Explorer
4. Scripting
5. Object scripting options
6. Include IF Not Exists Clause True

Answer 8

The "IF OBJECT_ID('table_name', 'U') IS NOT NULL" syntax is good, it can also be used for procedures: IF OBJECT_ID('procname', 'P') IS NOT NULL ...

... and triggers, views, etc... Probably good practice to specify type (U for table, P for prog, etc.. dont remember the exact letters for all types) in case your naming strandards allow procedures and tables to have similar names...

Furthermore, a good idea might be to create your own procedures that changes tables, with error handling proper to your environment. For example:

• prcTableDrop, Proc for droping a table
• prcTableColumnAdd, Proc for adding a column to a table
• prcTableColumnRename, you get the idea
• prcTableIndexCreate

Such procs makes creating repeatable (in same or other db) change scripts much easier.

/B