question

You can ask your default trace, if you like: DECLARE @enable INT; SELECT TOP 1 @enable = CONVERT(INT, value_in_use) FROM sys.configurations WHERE name = 'default trace enabled'; IF @enable = 1 --default trace is enabled BEGIN DECLARE @curr_tracefilename VARCHAR(500); DECLARE @base_tracefilename VARCHAR(500); DECLARE @indx INT; SELECT @curr_tracefilename = PATH FROM sys.traces WHERE is_default = 1; SET @curr_tracefilename = REVERSE(@curr_tracefilename); SET @indx = PATINDEX('%\%',@curr_tracefilename) SET @curr_tracefilename = REVERSE(@curr_tracefilename); SET @base_tracefilename = LEFT(@curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc'; SELECT Servername AS InstanceName ,ObjectName ,DatabaseName ,StartTime ,CASE ObjectType WHEN 8259 THEN 'CHECK' WHEN 8260 THEN 'DEFAULT' WHEN 8262 THEN 'FK' WHEN 8272 THEN 'SP' WHEN 8276 THEN 'TRIGGER Server' WHEN 8277 THEN 'TABLE' WHEN 8278 THEN 'VIEW' WHEN 16964 THEN 'DATABASE' WHEN 17235 THEN 'SCHEMA' WHEN 17475 THEN 'LOGIN' WHEN 17985 THEN 'FUNCTION (CLR-Aggregate)' WHEN 17993 THEN 'FUNCTION Inline' WHEN 18004 THEN 'FUNCTION' WHEN 19280 THEN 'PK' WHEN 20038 THEN 'FUNCTION Scalar' WHEN 20821 THEN 'UNIQUE' WHEN 21057 THEN 'ROLE Application' WHEN 21075 THEN 'SERVER' WHEN 21076 THEN 'TRIGGER SQL' WHEN 21313 THEN 'ASSEMBLY' WHEN 21333 THEN 'USER' WHEN 21572 THEN 'TRIGGER Database' WHEN 22868 THEN 'TYPE' WHEN 17232 THEN 'CLR Procedure' WHEN 21574 THEN 'CLR Table Valued Function' WHEN 21318 THEN 'CLR Scalar Function' WHEN 8275 THEN 'System Table' WHEN 22601 THEN 'INDEX' ELSE CAST(ObjectType AS VARCHAR(12)) + ' - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.de/udb9/html/42f85c50-34c9-49ca-955f-af9595e2707f.htm' END AS ObjectTypeName ,LoginName ,ISNULL(ApplicationName, N'?') AS ApplicationName ,CASE EventClass WHEN 46 THEN 'CREATE' WHEN 47 THEN 'DROP' WHEN 164 THEN 'ALTER' WHEN 104 THEN 'SQL Login - SQL Login: ' + ISNULL(TargetLoginName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END WHEN 105 THEN 'Windows Login - Login: ' + ISNULL(TargetLoginName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END WHEN 108 THEN 'Srvrolemember - Role: ' + ISNULL(RoleName, '?') + ', Login: ' + ISNULL(TargetLoginName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END WHEN 109 THEN 'DBuser - User: ' + ISNULL(TargetUserName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' WHEN 3 THEN 'Grant database access' WHEN 4 THEN 'Revoke database access' ELSE '?' END WHEN 110 THEN 'DBRolemember - DB Role: ' + ISNULL(RoleName, '?') + ', DB User: ' + ISNULL(TargetUserName, '?') + ', Action: ' + CASE EventSubClass WHEN 1 THEN 'ADD' WHEN 2 THEN 'DROP' ELSE '?' END ELSE CAST(EventClass AS VARCHAR(3)) + ' - ?' END AS DDLoperation FROM ::fn_trace_gettable(@base_tracefilename,DEFAULT) WHERE ((EventClass IN (46, 47, 164) AND EventSubClass = 0) OR (EventClass IN (104, 105, 108, 109, 110))) AND (DatabaseName 'tempdb' OR DatabaseName IS NULL) AND (NOT ObjectType IN(21587) OR ObjectType IS NULL); -- 21587 = Statistic END