Technical Article

READ Data from default Trace

,

1) Copy and Paste the script
2) Compile the procedure
3) Run the procedure
Let me know how it works!
CREATE PROC USP_ALTEROBJECTS @dbname varchar(100) = ''
as
BEGIN
DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

PRINT @FileName
IF @dbname  NULL
BEGIN
SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       te.Name AS EventName,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName, 
       gt.IsSystem
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in (164) AND DatabaseID = db_id(@dbname)
ORDER BY StartTime DESC;
END
ELSE

BEGIN

SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       te.Name AS EventName,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName, 
       gt.IsSystem
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in (164) AND DatabaseID = db_id()
ORDER BY StartTime DESC;
END

END;

/* TO RUN the PRocedure */
EXEC USP_ALTEROBJECTS @dbname = 'user_db'

EXEC USP_ALTEROBJECTS

Rate

3.5 (4)

Share

Share

Rate

3.5 (4)