…With alliteration.

Hey there and thanks for reading. Today I wanted to talk about a few recent additions I made to one of the SQL schemas I run! I was tired of manually adding a LastWriteTime and LastModifiedBy column to each table as they were created in the database, so naturally, I wanted to automate it.

This is a two part process, and it might be a little hacky but it works well enough in development and staging databases. I’ll need to see what the overhead is like in production, but I don’t anticipate any issues.

First up, we need a stored procedure to add 2 columns to a table. Nice and easy:

USE Development;
GO

ALTER PROCEDURE AddAuditColumns
@TableName VARCHAR(MAX)

AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql VARCHAR(MAX)
BEGIN
SET @sql = 'ALTER TABLE ' + @TableName + 'ADD LastWriteTime smalldatetime NOT NULL DEFAULT GETDATE()'
-- Change to NOW() for MySQL instead of SQLServer
EXEC(@sql)

SET @sql = 'ALTER TABLE ' + @TableName + 'ADD LastModifiedBy VARCHAR(60) NULL'
-- For purposes of the demo i'll allow null values
EXEC(@sql)
END
END

All of the code in this demo is available on my github page.

Cool, so that’s the easy part, you can execute that by passing any table in your schema to it and it’ll add the columns. LastWriteTime will have a default value of the time of execution or row addition. LastModifiedBy has no default due to how we use it, but you could set it to a value if you so choose.

The next part was a bit more challenging to figure out, I needed to parse event data from the CREATE_TABLE event and determine how to get the name parameter passed to it.

Event data is captured in a trigger by assigning EVENTDATA() to an XML variable. The data comes in a pretty easy to consume XML structure (i’ve formatted this one to make it look pretty, it’s normally displayed without whitespace or linebreaks).

The below event data is captured from the simple line:
CREATE TABLE MyTable (Integer int);

<EVENT_INSTANCE>
    	<EventType>CREATE_TABLE</EventType>
    	<PostTime>2015-09-22T15:40:15.827</PostTime>
    	<SPID>53</SPID>
    	<ServerName>SQLDEV01</ServerName>
    	<LoginName>Domain\Colin</LoginName>
    	<UserName>dbo</UserName>
    	<DatabaseName>Development</DatabaseName>
    	<SchemaName>dbo</SchemaName>
    	<ObjectName>MyTable</ObjectName>
    	<ObjectType>TABLE</ObjectType>
    	<TSQLCommand>
        		<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="        ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
        		<CommandText>CREATE TABLE MyTable (Integer int);</CommandText>
    	</TSQLCommand>
</EVENT_INSTANCE>

As you can see, we have a lot to work with in this data set. In this example, we’re going to be particularly interested in the <ObjectName> object, since that contains the table name that we’ll want to pass to our stored procedure. Let’s create a trigger to do just that!

Use Development;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [TRIG_ADD_AUDIT_ON_TABLE_CREATE] ON DATABASE
	FOR CREATE_TABLE

AS
BEGIN
	DECLARE @data XML
	DECLARE @tablename varchar(max)

	SET @data = EVENTDATA()
	SET @tablename = CONVERT(varchar(max), @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)'))
	EXEC AddAuditColumns(@tablename)
END

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [TRIG_ADD_AUDIT_ON_TABLE_CREATE] ON DATABASE
GO

That trigger above will snag the EVENTDATA() from the CREATE_TABLE event and add the columns defined in the stored procedure. This is a very extensible way to add columns on create and the best I could come up with on the fly.

Worth noting! If you want to enable this audit creation on the entire schema, you’ll need to modify the Trigger create a little bit, change:

ON DATABASE
to
ON SCHEMA

If there’s feedback or issues with the above scripts i’d love to see some pull requests on Github or just leave a comment.