CREATE VIEW dbo.DefaultTrace
AS
WITH ObjectTypes
/* http://msdn.microsoft.com/en-us/library/ms180953.aspx */ AS (SELECT [ObjectTypeId],
[ObjectTypeName]
FROM ( VALUES ( 8259, 'Check Constraint'),
( 8260, 'Default (constraint or standalone)'),
( 8262, 'Foreign-key Constraint'), ( 8272, 'Stored Procedure'),
( 8274, 'Rule'), ( 8275, 'System Table'), ( 8276, 'Trigger on Server'),
( 8277, '(User-defined) Table'), ( 8278, 'View'),
( 8280, 'Extended Stored Procedure'), ( 16724, 'CLR Trigger'),
( 16964, 'Database'), ( 16975, 'Object'), ( 17222, 'FullText Catalog'),
( 17232, 'CLR Stored Procedure'), ( 17235, 'Schema'), ( 17475, 'Credential'),
( 17491, 'DDL Event'), ( 17741, 'Management Event'),
( 17747, 'Security Event'), ( 17749, 'User Event'),
( 17985, 'CLR Aggregate Function'),
( 17993, 'Inline Table-valued SQL Function'), ( 18000, 'Partition Function'),
( 18002, 'Replication Filter Procedure'),
( 18004, 'Table-valued SQL Function'), ( 18259, 'Server Role'),
( 18263, 'Microsoft Windows Group'), ( 19265, 'Asymmetric Key'),
( 19277, 'Master Key'), ( 19280, 'Primary Key'), ( 19283, 'ObfusKey'),
( 19521, 'Asymmetric Key Login'), ( 19523, 'Certificate Login'),
( 19538, 'Role'), ( 19539, 'SQL Login'), ( 19543, 'Windows Login'),
( 20034, 'Remote Service Binding'),
( 20036, 'Event Notification on Database'), ( 20037, 'Event Notification'),
( 20038, 'Scalar SQL Function'), ( 20047, 'Event Notification on Object'),
( 20051, 'Synonym'), ( 20549, 'End Point'),
( 20801, 'Adhoc Queries which may be cached'),
( 20816, 'Prepared Queries which may be cached'),
( 20819, 'Service Broker Service Queue'), ( 20821, 'Unique Constraint'),
( 21057, 'Application Role'), ( 21059, 'Certificate'), ( 21075, 'Server'),
( 21076, 'Transact-SQL Trigger'), ( 21313, 'Assembly'),
( 21318, 'CLR Scalar Function'), ( 21321, 'Inline scalar SQL Function'),
( 21328, 'Partition Scheme'), ( 21333, 'User'),
( 21571, 'Service Broker Service Contract'), ( 21572, 'Trigger on Database'),
( 21574, 'CLR Table-valued Function'),
( 21577, 'Internal Table (For example, XML Node Table, Queue Table.)'),
( 21581, 'Service Broker Message Type'), ( 21586, 'Service Broker Route'),
( 21587, 'Statistics'), ( 21825, 'User'), ( 21827, 'User'), ( 21831, 'User'),
( 21843, 'User'), ( 21847, 'User'), ( 22099, 'Service Broker Service'),
( 22601, 'Index'), ( 22604, 'Certificate Login'), ( 22611, 'XMLSchema'),
( 22868, 'Type') ) AS ObjectTypes ([ObjectTypeId], [ObjectTypeName])),
s AS (SELECT base_tracefilename
FROM sys.traces (NOLOCK) t
CROSS APPLY (SELECT REVERSE (PATH) AS rv) a
CROSS APPLY (SELECT PATINDEX ('%\%', rv) AS indx) b
CROSS APPLY (SELECT LEFT (PATH, LEN (PATH) - indx) + '\log.trc' AS base_tracefilename) c
WHERE is_default = 1)
SELECT te.Name AS EVENT,
DatabaseID,
DB_NAME(DatabaseID) AS DBName,
StartTime,
SERVERNAME,
HostName,
SPID,
ApplicationName,
LoginName,
ObjectType,
ot.ObjectTypeName,
OBJECT_NAME(ObjectID, DatabaseID) AS CurrentObjectName,
ObjectName,
ObjectID,
T.Duration / 1000000 AS DurationSec,
CASE WHEN DataFileAutoGrow = 1
OR DataFileAutoShrink = 1
OR LogFileAutoGrow = 1
OR LogFileAutoShrink = 1 THEN (IntegerData * 8) / 1024
END AS [GrowthMB],
ObjectCreated,
ObjectDeleted,
DataFileAutoGrow,
DataFileAutoShrink,
LogFileAutoGrow,
LogFileAutoShrink,
c.textdata,
CASE WHEN t.textdata IS NOT NULL THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + c.textdata+ CHAR (13) AS [Q!1!Q!CDATA]
FOR XML EXPLICIT,
TYPE)
END textdataxml,
t.EventClass,
EventSubClass,
t.IntegerData
FROM FN_TRACE_GETTABLE((SELECT base_tracefilename FROM s), DEFAULT) t
CROSS APPLY (SELECT CAST(textdata AS VARCHAR(MAX)) AS textdata,
CASE WHEN T.EventClass = 164 THEN 1
ELSE 0
END AS ObjectAltered,
CASE WHEN T.EventClass = 46 THEN 1
ELSE 0
END AS ObjectCreated,
CASE WHEN T.EventClass = 47 THEN 1
ELSE 0
END AS ObjectDeleted,
CASE WHEN T.EventClass = 92 THEN 1
ELSE 0
END AS DataFileAutoGrow,
CASE WHEN T.EventClass = 94 THEN 1
ELSE 0
END AS DataFileAutoShrink,
CASE WHEN T.EventClass = 93 THEN 1
ELSE 0
END AS LogFileAutoGrow,
CASE WHEN T.EventClass = 95 THEN 1
ELSE 0
END AS LogFileAutoShrink) c
LEFT OUTER JOIN sys.trace_events TE (NOLOCK) ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN ObjectTypes ot ON ot.ObjectTypeId = T.ObjectType
WHERE EXISTS ( SELECT *
FROM sys.configurations (NOLOCK)
WHERE name = 'default trace enabled'
AND CONVERT(INT, value_in_use) = 1 )