I'm trying to create a DDL Trigger dynamically so I can create a job that checks each database and creates the trigger if it's not already in place. To do this, I'm using sp_foreachdb. The code works on it's on but I can't get it to work with sp_foreachdb so I'm guessing I've missed a single quote. However, I'm struggling to find the error. Any help is greatly appreciated.
EXEC master.dbo.sp_foreachdb
'
IF NOT EXISTS (SELECT * FROM sys.triggers T WHERE T.name =
''Database_DDL_Audit'') AND ''?'' <> ''TempDB''
BEGIN
EXEC
(
''
CREATE TRIGGER Database_DDL_Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
DECLARE @EventDataXml XML
DECLARE @SchemaName SYSNAME
DECLARE @ObjectName SYSNAME
DECLARE @EventType SYSNAME
SET @EventDataXml = EVENTDATA()
SELECT
@EventType = @EventDataXml.value(''''(/EVENT_INSTANCE/EventType)[1]'''', ''''SYSNAME'''')
, @SchemaName = @EventDataXml.value(''''(/EVENT_INSTANCE/SchemaName)[1]'''', ''''SYSNAME'''')
, @ObjectName = @EventDataXml.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''', ''''SYSNAME'''')
INSERT [ServerAdmin].[dbo].[ServerChangeLog]
(
[CreateDate]
, [LoginName]
, [ComputerName]
, [ProgramName]
, [DBName]
, [SQLEvent]
, [SchemaName]
, [ObjectName]
, [SQLCmd]
, [XmlEvent]
)
SELECT GETDATE()
, SUSER_NAME()
, HOST_NAME()
, PROGRAM_NAME()
, @EventDataXml.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', ''''SYSNAME'''')
, @EventType
, @SchemaName
, @ObjectName
, @EventDataXml.value(''''(/EVENT_INSTANCE/TSQLCommand)[1]'''', ''''NVARCHAR(MAX)'''')
, @EventDataXml
''
)
END
'
The error I'm getting is
Msg 102, Level 15, State 1, Procedure Database_DDL_Audit, Line 40 [Batch Start Line 0]
Incorrect syntax near '@EventDataXml'.
Best Answer
You are missing an
END
. See updated as below:Note: Use Aaron's version of
sp_msforeachdb