After speaking yesterday and recanting my story (for about the 100th time) about EventData being XML as a “nefarious plot” to require DBAs to learn XML, I wrote this fairly simple XQuery (actually dead simple XPath) function to change EventData into a rowset. It works based on the fact that you've deposited EventData into a table that looks like this:
CREATE TABLE ddl_log ( id int primary key identity, data XML);
CREATE TRIGGER mytrigON DATABASEFOR CREATE_TABLE ASINSERT ddl_log VALUES(EventData);
This makes your table into a rowset. You could also use a variation of it in your event notification handler, DDL trigger itself. Just leave out the cross apply. I thought I'd seen this before, but can never seem to ever have found it. Now that I've done this, ....learn XML why don't 'ya... There can only be more of it in future. Cheers.
SELECT id, Tab.Col.value('./EventType[1]','nvarchar(50)') AS 'EventType', Tab.Col.value('./PostTime[1]','datetime') AS 'PostTime', Tab.Col.value('./SPID[1]','nvarchar(50)') AS 'SPID', Tab.Col.value('./ServerName[1]','nvarchar(50)') AS 'ServerName', Tab.Col.value('./LoginName[1]','nvarchar(50)') AS 'LoginName', Tab.Col.value('./UserName[1]','nvarchar(50)') AS 'UserName', Tab.Col.value('./DatabaseName[1]','nvarchar(128)') AS 'DatabaseName', Tab.Col.value('./SchemaName[1]','nvarchar(128)') AS 'SchemaName', Tab.Col.value('./ObjectName[1]','nvarchar(128)') AS 'ObjectName', Tab.Col.value('./ObjectType[1]','nvarchar(50)') AS 'ObjectType', Tab.Col.value('./TSQLCommand[1]/CommandText[1]','nvarchar(4000)') AS 'CommandText', Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_NULLS','nvarchar(3)') AS 'ANSI_NULLS_OPTION', Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_NULL_DEFAULT','nvarchar(3)') AS 'ANSI_NULL_DEFAULT_OPTION', Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_PADDING','nvarchar(3)') AS 'ANSI_PADDING_OPTION', Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@QUOTED_IDENTIFIER','nvarchar(3)') AS 'QUOTED_IDENTIFIER_OPTION', Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ENCRYPTED_OPTION','nvarchar(4)') AS 'ENCRYPTED_OPTION'FROM ddl_logCROSS APPLY data.nodes('/EVENT_INSTANCE') AS Tab(Col)GO
-- standalone trigger
CREATE TRIGGER mytrigON DATABASEFOR CREATE_TABLE ASDECLARE @x XMLSET @x = Eventdata()SELECT Tab.Col.value('./EventType[1]','nvarchar(50)') AS 'EventType', Tab.Col.value('./PostTime[1]','datetime') AS 'PostTime', -- rest of columns deleted for brevityFROM @x.nodes('/EVENT_INSTANCE') AS Tab(Col)GO
Theme design by Jelle Druyts
Pick a theme: BlogXP sqlx BlogXP sqlx
Powered by: newtelligence dasBlog 2.0.7226.0
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.
© Copyright 2008, Bob Beauchemin
E-mail