SQL 2005 introduced DDL Triggers, where you could catch events on your DDL statements in SQL, and hopefully most DBA’s are catching them, at least to a table and have some kind of report on who is adding, changing and deleting what objects.
What I wanted to do was capture that, but also keep and automatic running log in SVN (subversion) source control. Here is how I went about it.
First, you need SVN set up somewhere. We use unfuddle (http://unfuddle.com/) – which we also use for Agile BI stuff, but that is another post 🙂 and unfuddle also let’s us do SVN in the cloud, but you could do it locally or internally or whatever you’d like. I had to create a user for this, to do automatic commits, and give it rights to commit to the repo.
Second, you probably already want your DDL triggers set up to capture events on your databases, and write to a table. Writing to central DB on a server is probably a good idea. If you have that, or something similar, I then created a trigger on that table, to capture the INSERTED action and take that record that was just inserted and parse what I need, and automatically commit to SVN.
But first, you want to script out all your objects for a database to a folder, say C:DBSchemas
I created a folder structure to help me
DBSchemasServerNameDatabaseNameObjectType
and then scripted out all the objects to each folder for ObjectType (Tables, StoredProcedures, Functions, and Views)
Once that was done, I did an initial import into SVN, and make the folder I was working with a working copy. Then the funs starts.
I created a couple of procs (which I found online, links to the blogs are below) to CREATE and DELETE files from T-SQL using OLE automation
CREATE/APPEND (http://sqlsolace.blogspot.com/2009/01/ole-automation-write-text-file-from.html)
CREATE PROCEDURE [Utils].[usp_OLEWriteFile] (@FileName varchar(1000), @TextData NVARCHAR(MAX),@FileAction VARCHAR(12)) AS
BEGIN
DECLARE @OLEfilesytemobject INT
DECLARE @OLEResult INT
DECLARE @FileID INT
EXECUTE @OLEResult =
sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT
IF @OLEResult 0
PRINT 'Error: Scripting.FileSystemObject'
-- check if file exists
EXEC sp_OAMethod @OLEfilesytemobject, 'FileExists', @OLEresult OUT, @FileName
-- if file esists
IF (@OLEresult=1 AND @FileAction = 'APPEND') OR (@OLEresult=0)
BEGIN
IF (@FileAction = 'CREATENEW')
PRINT 'New file specified, creating...'
IF (@OLEresult=1 AND @FileAction = 'APPEND')
PRINT 'File exists, appending...'
IF (@OLEresult=0 AND @FileAction = 'APPEND')
PRINT 'File doesnt exist, creating...'
-- open file
EXECUTE @OLEResult = sp_OAMethod @OLEfilesytemobject, 'OpenTextFile', @FileID OUT,
@FileName, 8, 1
IF @OLEResult 0 PRINT 'Error: OpenTextFile'
-- write Text1 to the file
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @TextData
IF @OLEResult 0
PRINT 'Error : WriteLine'
ELSE
PRINT 'Success'
END
IF (@OLEresult=1 AND @FileAction = 'CREATENEW')
PRINT 'File Exists, specify APPEND if this is the desired action'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @OLEfilesytemobject
END
GO
DELETE (http://www.kodyaz.com/articles/delete-file-from-sql-server-xp-cmdshell-ole-automation-procedures.aspx)
DECLARE @Result int
DECLARE @FSO_Token int
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'C:delete-me-file.txt'
EXEC @Result = sp_OADestroy @FSO_Token
You need to make sure OLE Automation is on. You need to make sure that the account you are running SQL as has modify rights to your DBSchemas folder.
But the crux of the solution is the trigger that gets the DDL info, and writes/deletes the files and SVN Add/Del/Commit’s the file. Now this is some ugly 1 hour SQL script craziness, tons of IF statements, etc. It could be improved, but it works, and it is a start, it can be modified and tweaked to do whatever you want. Note, if your SVN repo isn’t authenticated you don’t need the username/password for the SVN commands.
You can see, it gets the DDL, checks the events (and I have it limited to one database), and it checks what type of object and what operation, and for and add, it adds and commits, for a updated, deletes file, recreates it, and commits, and for a delete it does and svn delete and commit. Pretty easy 🙂
CREATE TRIGGER DDLRevisionHistory
ON dbo.DDLEventLog
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EventType VARCHAR(50)
DECLARE @DatabaseName VARCHAR(50)
DECLARE @ServerName VARCHAR(50)
DECLARE @ObjectName VARCHAR(100)
DECLARE @SchemaName VARCHAR(10)
DECLARE @CommandText VARCHAR(MAX)
DECLARE @LoginName VARCHAR(50)
SELECT
@EventType = EventInstance.value('(//EventType)[1]', 'varchar(50)'),
@DatabaseName = EventInstance.value('(//DatabaseName)[1]', 'varchar(50)'),
@ServerName = EventInstance.value('(//ServerName)[1]', 'varchar(50)'),
@ObjectName = EventInstance.value('(//ObjectName)[1]', 'varchar(50)'),
@SchemaName =EventInstance.value('(//SchemaName)[1]', 'varchar(50)'),
@CommandText = EventInstance.value('(//TSQLCommand//CommandText)[1]', 'varchar(max)'),
@LoginName = EventInstance.value('(//LoginName)[1]', 'varchar(50)')
FROM inserted
DECLARE @filepath VARCHAR(8000)
SET @filepath = 'C:DBSchemas' + @ServerName + '' + @DatabaseName + ''
IF (
@EventType = 'CREATE_VIEW' OR @EventType = 'ALTER_VIEW' OR @EventType = 'DROP_VIEW'
OR @EventType = 'CREATE_TABLE' OR @EventType = 'ALTER_TABLE' OR @EventType = 'DROP_TABLE'
OR @EventType = 'CREATE_PROCEDURE' OR @EventType = 'ALTER_PROCEDURE' OR @EventType = 'DROP_PROCEDURE'
OR @EventType = 'CREATE_FUNCTION' OR @EventType = 'ALTER_FUNCTION' OR @EventType = 'DROP_FUNCTION'
)
AND @DatabaseName = 'YourDatabase' BEGIN
-- write out new file to correct folder
IF CHARINDEX('VIEW',@EventType) > 0 BEGIN
SET @filepath = @filepath + 'Views' + @SchemaName + '.' + @ObjectName + '.View.sql'
END
IF CHARINDEX('TABLE',@EventType) > 0 BEGIN
SET @filepath = @filepath + 'Tables' + @SchemaName + '.' + @ObjectName + '.Table.sql'
END
IF CHARINDEX('PROCEDURE',@EventType) > 0 BEGIN
SET @filepath = @filepath + 'StoredProcedures' + @SchemaName + '.' + @ObjectName + '.StoredProcedure.sql'
END
IF CHARINDEX('FUNCTION',@EventType) > 0 BEGIN
SET @filepath = @filepath + 'Views' + @SchemaName + '.' + @ObjectName + '.UserDefinedFunction.sql'
END
IF CHARINDEX('CREATE',@EventType) > 0 BEGIN
-- create file
EXEC dbo.usp_OLEWriteFile @filepath,@CommandText,'CREATENEW'
-- svn add
DECLARE @instrAdd VARCHAR(4000)
SET @instrAdd='svn add ' + @filepath + ' --username dbschema --password yourpassword'
EXEC xp_cmdshell @instrAdd
-- svn commit
DECLARE @instrCommitAdd VARCHAR(4000)
SET @instrCommitAdd='svn commit ' + @filepath + ' --message "added by '+ @LoginName +'" --username dbschema --password yourpassword'
EXEC xp_cmdshell @instrCommitAdd
END
IF CHARINDEX('ALTER',@EventType) > 0 BEGIN
--delete and readd file
EXEC dbo.usp_OLEDeleteFile @filepath
EXEC dbo.usp_OLEWriteFile @filepath,@CommandText,'CREATENEW'
-- svn commit
DECLARE @instrCommitChange VARCHAR(4000)
SET @instrCommitChange='svn commit ' + @filepath + ' --message "changed by '+ @LoginName + '" --username dbschema --password yourpassword'
--PRINT @instrCommitChange
EXEC xp_cmdshell @instrCommitChange
END
IF CHARINDEX('DROP',@EventType) > 0 BEGIN
-- svn delete
DECLARE @instrDel VARCHAR(4000)
SET @instrDel='svn delete ' + @filepath + ' --username dbschema --password yourpassword'
EXEC xp_cmdshell @instrDel
-- svn commit
DECLARE @instrCommitDel VARCHAR(4000)
SET @instrCommitDel='svn commit ' + @filepath + ' --message "deleted by '+ @LoginName +'" --username dbschema --password yourpassword'
EXEC xp_cmdshell @instrCommitDel
END
END
END
as you can see you can create a homegrown revision history of your DDL objects in SQL . I have tested this on the basic operations, no renames, etc using the GUI, but if you do use it, you might want to wrap it all in exception handling just to be on the safe side.
Happy DBA’ing 🙂
Steve Novoselac's Blog 