Over the last several months, I’ve had to write patch scripts for database updates. Writing the scripts tends to be easy and I came up with a fairly effective way to implement changes but writing the actual script got to be tedious. So, like any good programmer, I automated the task.

My Patch Implementation

There are a lot of ways to write patch scripts, but one main concern is that you don’t overwrite procedures with old versions. For my environment, I came up with a fairly simple solution for procedures and user defined functions. Every update to a procedure or UDF creates a time stamped named version of itself. If I was generating a patched version of auditing.GeneralLogInsert today, I would check to see if auditing.[GeneralLogInsert-2011-06-22] exists. If it does, then I don’t proceed any further with that stored procedure. If it doesn’t, then I populate an nvarchar(max) variable with the create statement for the procedure/UDF, execute a rename on the existing object and then execute the create. It looks something like this:

IF NOT EXISTS(SELECT * FROM sys.all_objects WHERE name = GeneralLogInsert-2011-06-22' and schema_name(schema_id) = 'auditing')
BEGIN
declare @SQLCommand nvarchar(max)
SET @SQLCommand = '-- =============================================
-- Author:        Josef Finsel
-- Create date: 2010-11-20
-- Description:    Record Log Information
-- =============================================
CREATE PROCEDURE auditing.GeneralLogInsert
           (@DomainName nvarchar(50)
           ,@FolderPath nvarchar(2083)
           ,@PageName nvarchar(2083)
           ,@Method nvarchar(10)
           ,@IPAddress nvarchar(50)
           ,@UserID nvarchar(255)
           ,@LogEvent nvarchar(100)
           ,@LogMessage nvarchar(max)
           ,@ParameterInformation xml)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
declare @InternalUserID bigint
INSERT INTO [auditing].[GeneralLog]
           ([DateLogged]
           ,[DomainName]
           ,[FolderPath]
           ,[PageName]
           ,[Method]
           ,[IPAddress]
           ,InternalUserID
           ,[LogEvent]
           ,[LogMessage]
           ,[ParameterInformation])
     VALUES
           (getdate()
           ,@DomainName
           ,@FolderPath
           ,@PageName
           ,@Method
           ,@IPAddress
           ,@InternalUserID
           ,@LogEvent
           ,@LogMessage
           ,@ParameterInformation)
END
'
exec sp_rename 'auditing.GeneralLogInsert, GeneralLogInsert-2011-06-22', 'object';
EXEC (@SQLCommand)
END
GO

To generate a script like this, I used to have to go into SSMS, right click on the object and script as create to new query window, remove the header information, change all sinqle quotes to double single quotes and then put it into the script. On more than one occasion I found myself having to fix scripts simply because I didn’t match the object names up correctly. Now I don’t have to do this, I can use my utility to create the script block for me.

Running the Utility

The heart of the utility is actually in a class that can be expanded on and used in a number of places, but I have put the class inside an EXE that can be called. From there I can redirect the output into a file.

GenerateSQLPatch.exe -? -c[onnection]:dbConnection -s[chema]:SchemaName -o[bjectname]:ObjectName        -update
-? shows this message
-c[onnection] the connection string used to connect to the server
-s[chema] the schema name of the object
-o[bjectname] name of the object to be created)
-update Flag to indicate whether this is a new object or update of an existing

Get the Code

I’ve posted the code in an open project on GitHub. You can find it here. Feel free to modify it to suit your needs and to offer up contributions for improving it.