Defensive SQL Updates
One of my recent tasks, well before my month off anyway, was to upgrade a number of similar SQL Server 2005 databases to a new structure. The databases had all started off the same, and were going to end up the same, but had been subjected to a differing number of bug fixes and enhancements along the way.If I was updating just one database I would have used the excellent Redgate SQL Compare (of which more in a future post) but as I had to update many then this would not have been the most reliable way.Instead, I wrote defensive SQL, that is SQL which checks for impact before doing it. So, before adding a column for instance, it checks to see if that column exists, and only attempts to add it if it doesn't - thus preventing errors. The whole update script was wrapped in a Transaction, and by making use use of a variable @bError of type BIT to track any errors, the last statement could COMMIT or ROLLBACK the transaction as appropriate.So, for example, to add a new column to a table, here is the script I used:
IF @bError = 0
BEGIN
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn')
BEGIN ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn')
BEGIN
PRINT 'Error Adding column ColumnName to table MyTable'
SET @bError = 1
END
ELSE
BEGIN
PRINT 'Added column ColumnName to table MyTable'
END
END
ENDBy checking for the column's existance after applying the change, I could determine if an error had occurred, and set the @bError flag accordingly. I used the Print statements to produce a log of exactly what stages had been carried out on each database, using the function DB_NAME() to output the name of the current database at the beginning of the log.
IF @bError = 0
BEGIN
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn')
BEGIN ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'MyTable' AND Column_Name = 'MyNewColumn')
BEGIN
PRINT 'Error Adding column ColumnName to table MyTable'
SET @bError = 1
END
ELSE
BEGIN
PRINT 'Added column ColumnName to table MyTable'
END
END
ENDBy checking for the column's existance after applying the change, I could determine if an error had occurred, and set the @bError flag accordingly. I used the Print statements to produce a log of exactly what stages had been carried out on each database, using the function DB_NAME() to output the name of the current database at the beginning of the log.
