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 (@way0utwest) 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
CREATE TRIGGER add_extended_property ON DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW AS 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)[1]' ,'nvarchar(3750)') , @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]' ,'nvarchar(200)') , @PostTime = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]' ,'nvarchar(23)') , @UserName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]' ,'nvarchar(100)') , @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]' ,'nvarchar(128)') , @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'nvarchar(128)') , @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]' ,'nvarchar(128)') , @ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]' ,'nvarchar(128)'); SET @DDLCode = REPLACE(@PostTime,'T',' ') + N' : ' + @EventType + N' (' + @UserName + N')'; IF LEFT(@EventType, 4) = 'DROP' BEGIN EXEC sys.sp_addextendedproperty @name = @DDLCode , @value = @Command , @level0type = NULL; END ELSE BEGIN EXEC sys.sp_addextendedproperty @name = @DDLCode , @value = @Command , @level0type = N'Schema' , @level0name = @SchemaName , @level1type = @ObjectType , @level1name = @ObjectName; END; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<EVENT_INSTANCE> <EventType>CREATE_TABLE</EventType> <PostTime>2015-05-28T18:32:39.273</PostTime> <SPID>52</SPID> <ServerName>[Servername]</ServerName> <LoginName>[Servername]\[Username]</LoginName> <UserName>dbo</UserName> <DatabaseName>[Databasename]</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>DDL_Trigger_Test</ObjectName> <ObjectType>TABLE</ObjectType> <TSQLCommand> <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> </TSQLCommand> </EVENT_INSTANCE> |
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:
|
1 2 3 4 5 6 7 8 |
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!
Nice job. Good idea and great post.
I actually tried to do this exact thing before coming across your post. Our code looks pretty similar, however when I try to create a table I get the message “Property cannot be added. Property ‘MS_Creator’ already exists for ‘dbo.Table_1’.” (where MS_Creator is the ‘name’ of the EP). That’s what prompted me to search the web and came across your post. My code is below, any thoughts would be appreciated.
USE [master];
GO
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = ‘ddl_trigger_table_create_alter_WhoDidIt’)
DROP TRIGGER ddl_trigger_table_create_alter_WhoDidIt
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trigger_table_create_alter_WhoDidIt
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@EventData XML = EVENTDATA();
–insert SandBox.dbo.EventDataFromDDL (eventdataxml)
— SELECT
— @EventData ;
DECLARE @TableName SYSNAME; — Name of table
set @TableName = @EventData.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘NVARCHAR(255)’);
DECLARE @dbName NVARCHAR(200); — Name of table
set @dbName = @EventData.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘NVARCHAR(255)’);
DECLARE @SchemaName NVARCHAR(200); — Name of table
set @SchemaName = @EventData.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘NVARCHAR(255)’);
Declare @LoginName nvarchar(200);
set @LoginName = @EventData.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘NVARCHAR(255)’);
DECLARE @MS_Description NVARCHAR(200) = NULL;
DECLARE @module_name_var NVARCHAR(500) = QUOTENAME(@DbName) + ‘.sys.extended_properties’;
DECLARE @module_name_var_add NVARCHAR(500) = QUOTENAME(@DbName) + ‘.sys.sp_addextendedproperty’;
DECLARE @module_name_var_upd NVARCHAR(500) = QUOTENAME(@DbName) + ‘.sys.sp_updateextendedproperty’;
declare @sql nvarchar(1000)
set @sql= N’SET ‘ + @MS_Description + ‘ = (SELECT CAST(Value AS NVARCHAR(200)) AS [MS_Creator]
FROM ‘ + @module_name_var + ‘AS ep
WHERE ep.major_id = OBJECT_ID(‘ + @TableName + ‘)
AND ep.name = N”MS_Creator” AND ep.minor_id = 0); ‘
exec sp_executesql @sql
IF @MS_Description IS NULL
BEGIN
EXEC @module_name_var_add
@name = N’MS_Creator’,
@value = @LoginName,
@level0type = N’SCHEMA’,
@level0name = @SchemaName,
@level1type = N’TABLE’,
@level1name = @TableName;
END
ELSE
BEGIN
EXEC @module_name_var_upd
@name = N’MS_Creator’,
@value = @LoginName,
@level0type = N’SCHEMA’,
@level0name = @SchemaName,
@level1type = N’TABLE’,
@level1name = @TableName;
END
END
GO
I suggest you add something that makes the extended property unique, like a timestamp or GUID as they have to be unique. The errormessage you are receiving hints at duplication.