Tracking Temp Tables using DDL triggers

At my SQL Saturday session this past weekend, I had a great question about how to track Temp Tables - such as those made by a developer and never cleaned up.

Using the test framework I had presented, I went back and tried it again, and no, they aren't tracked by the DDL trigger. However, it was possible to quickly and easily write a "capture everything" extended events session to record the SQL Statements that created them.

With a little filtering, it would be easy enough to restrict that by database.

Here's my test:

-- Script inspired by
-- www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
USE TEMPDB;
GO
IF EXISTS (
SELECT *
FROM tempdb.SYS.TRIGGERS
WHERE NAME = 'DDLTrigger_Sample'
)
BEGIN
DROP TRIGGER DDLTrigger_Sample
ON DATABASE;
END
GO
USE MASTER;
GO
IF EXISTS (
SELECT 1
FROM sys.server_triggers
WHERE NAME = 'ddl_trig_alter_db'
AND parent_class_desc = 'SERVER'
)
BEGIN
DROP TRIGGER ddl_trig_alter_db
ON ALL SERVER;
END
GO
IF EXISTS (
SELECT 1
FROM sys.databases
WHERE NAME = 'AuditSampleDB'
)
DROP DATABASE AuditSampleDB;
GO
CREATE DATABASE AuditSampleDB;
GO
EXEC AuditSampleDB.dbo.sp_changedbowner 'SA';
GO
USE AuditSampleDB;
GO
CREATE TABLE dbo.DDLEvents (
EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
EventType NVARCHAR(64),
EventDDL NVARCHAR(MAX),
EventXML XML,
DatabaseName NVARCHAR(255),
SchemaName NVARCHAR(255),
ObjectName NVARCHAR(255),
[ObjectID] INT,
HostName VARCHAR(64),
IPAddress VARCHAR(32),
ProgramName NVARCHAR(255),
LoginName NVARCHAR(255)
);
USE TEMPDB;
GO
CREATE TRIGGER DDLTrigger_Sample ON DATABASE
FOR CREATE_PROCEDURE,
ALTER_PROCEDURE,
DROP_PROCEDURE,
CREATE_TABLE,
ALTER_TABLE,
DROP_TABLE AS
BEGIN
SET NOCOUNT ON;
DECLARE @EventData XML = EVENTDATA();
DECLARE @ip VARCHAR(32) = (
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
);
INSERT AuditSampleDB.dbo.DDLEvents (
EventType,
EventDDL,
EventXML,
DatabaseName,
SchemaName,
ObjectName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME();
END
GO
USE TEMPDB;
GO
CREATE TABLE #TESTTEMP (C1 INT);
GO
DROP TABLE #TESTTEMP;
GO
CREATE TABLE ##TESTTEMP2 (C1 INT);
GO
DROP TABLE ##TESTTEMP2;
GO
USE MASTER;
GO
CREATE TABLE TEMPDB.DBO.ISTHISTRACKED (C1 INT);
GO
DROP TABLE TEMPDB.DBO.ISTHISTRACKED;
GO
SELECT LoginName,
EventType,
EventDDL,
EventXML
FROM AuditSampleDB.dbo.DDLEvents
ORDER BY EventDate
GO