How to get database name in a server-level DDL trigger

Question

I'm creating a Server level trigger that fires after a create table statement, and I want to print the database name and table name.

CREATE TRIGGER LogTempTables
ON ALL SERVER
AFTER CREATE_TABLE
AS 
BEGIN
SET NOCOUNT ON;

       DECLARE @EventData XML = EVENTDATA()
       DECLARE @TableName NVARCHAR(50) 
               = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(50)')
       DECLARE @Database NVARCHAR(50) = DB_NAME()

       PRINT @Database
       PRINT @TableName
END
GO

If I create a table in database ABC123 it prints:

master
TestTable

score 5 · Accepted Answer · answered Feb 25 '20 at 17:30

DB_NAME() will always return the context of the trigger, not the context of the event. But you're in luck: the EVENTDATA() contains the object name and the database name. Try adding this to your trigger (also you should be very careful about using 50 characters, because both tables and databases can have longer names).

DECLARE @db sysname = @EventData.value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname');
PRINT @db;

How to get database name in a server-level DDL trigger

1 Answers1