Sample Header Ad - 728x90

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

1 vote
1 answer
1031 views
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)', '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
Asked by Matthew (43 rep)
Feb 25, 2020, 05:13 PM
Last activity: Feb 25, 2020, 05:31 PM