Lazy Installation Scripts

One of my pet hates is working with installation / update scripts which don't check if objects exist before trying to drop them.

This is just lazy and (when) lots of objects are involved it makes reviewing the installation logs difficult due to the large number of "Cannot drop the view...." type errors. Too often I've heard the excuse "you can just ignore those errors, they're not important". They may not be important, but there's no reason the DBA or user running the script should have to see or be confused by them.

OK, so SQL Server doesn't have an equivalent of Oracle's one liner CREATE OR REPLACE but it's easy to check if an object exists before running the DROP / CREATE pair of statements. There are a few options available. If the object is a table or a view you can query INFORMATION_SCHEMA.TABLES to see if it already exists. For a wider coverage of objects you can query SYS.OBJECTS directly.

The approach can go something like this:

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES

WHERE table_name = 'myView')

DROP VIEW myView

GO

CREATE VIEW myView AS .....

This simple step will make for much cleaner install logs where the only errors are ones that are worthy of attention.