Use a DDL trigger to add extended properties
For a while I have been contemplating to start a SQL blog. I suppose it is only fair to give Steve Jones (@) some credit for it since SQLServerCentral has been my primary source for building up my SQL skills and every now and then he encourages people to write down what they care about – SQL.
So let’s dive right into it. Today I was faced with a problem that seems to resurface on a regular basis. You find a table and due to the lack of documentation (yeah, in a perfect world this would not be a problem) you have no idea who created it or how it gets populated. I recently stumbled across an article by Kenneth Fisher about using extended properties to handle meta data, so I took the idea a little further and did what I usually try to do: automate at least part of it. Hence the idea was born to create a DDL trigger to automatically add some vital information to tables and views.
Let’s look at the trigger code:
CREATE TRIGGER add_extended_property
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW
DECLARE @Command nvarchar(3750)
, @EventType nvarchar(200)
, @PostTime nvarchar(23)
, @UserName nvarchar(25)
, @SchemaName nvarchar(128)
, @DatabaseName nvarchar(128)
, @ObjectName nvarchar(128)
, @ObjectType nvarchar(128)
, @DDLCode nvarchar(100);
SELECT @Command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)' ,'nvarchar(3750)')
, @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)' ,'nvarchar(200)')
, @PostTime = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)' ,'nvarchar(23)')
, @UserName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)' ,'nvarchar(100)')
, @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)' ,'nvarchar(128)')
, @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)' ,'nvarchar(128)')
, @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)' ,'nvarchar(128)')
, @ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)' ,'nvarchar(128)');
SET @DDLCode = REPLACE(@PostTime,'T',' ') + N' : ' + @EventType + N' (' + @UserName + N')';
IF LEFT(@EventType, 4) = 'DROP'
@name = @DDLCode
, @value = @Command
, @level0type = NULL;
@name = @DDLCode
, @value = @Command
, @level0type = N'Schema'
, @level0name = @SchemaName
, @level1type = @ObjectType
, @level1name = @ObjectName;
You will notice that EVENTDATA() comes in very handy to grab all the meta data you need to call the stored procedure sys.sp_addextendedpropery which we are using to add properties to objects. If you create a trigger that just shows you the output from EVENTDATA() you will receive an XML that looks somewhat like this:
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE TABLE dbo.DDL_Trigger_Test(RowID INT)</CommandText>
In order to create a unique property name I chose to concatenate the timestamp, taken from EVENTDATA(), along with the event type and username from whoever executed the DDL statement. This also allows us to sort all properties by date. In addition to the unique property the trigger also saves the first 3750 characters of the code as value for the newly created property. Unfortunately the value column only holds up to 7500 bytes of data, at 2 byte / character that’s all we get – but it should cover most ALTER statements. If you really need to keep track of the whole shebang you will have to save our output to a seperate table.
Once the trigger is in place and you executed some CREATE / ALTER / DROP statements you will be able to query the catalog view sys.extended_properties. Since it does not make a whole lot of sense to add properties to an object you just dropped all DROP statements will be added at the DB level for future reference. Everything else will be stored under the object itself. Here is a very basic query to retrieve the information:
SELECT QUOTENAME(DB_NAME()) + ISNULL('.' + QUOTENAME(OBJECT_SCHEMA_NAME(pp.major_id)) + '.' + QUOTENAME(OBJECT_NAME(pp.major_id)),'') ObjectName
, CAST(LEFT(pp.name,22) AS DATETIME) ChangeDate
, SUBSTRING(pp.name, 27, CHARINDEX('(', pp.name) - 28) ChangeType
, UPPER(REPLACE(STUFF(pp.name, 1, CHARINDEX('(', pp.name),''),')','')) ChangeUser
, pp.value Code
FROM sys.extended_properties pp
WHERE ISDATE(LEFT(pp.name,22)) = 1
ORDER BY ObjectName, ChangeDate
Alternatively you can click your way through SSMS and find the information in there as well:
This is it. From now on you will be able to tell who created objects in your DB, or who changed the datatype on a column which causes your beloved SSIS package to fail the next time it is scheduled.
Please let me know if this is helpful, what you would do differently or any other thoughts you might have. Any input is – especially since is my first entry – greatly appreciated!