لطفا منتظر باشید ...
sp_list_trace
Because the trace routines are designed to allow you to start the trace and leave it running even if you close the connection, it's possible that you'll need a means of checking the status of the currently running traces. This is what sp_list_trace is for. Here's its code (Listing 21-6):
Listing 21-6 sp_list_trace.
USE master
GO
IF OBJECT_ID('sp_list_trace') IS NOT NULL
DROP PROC sp_list_trace
GO
CREATE PROC sp_list_trace @TraceId varchar(10)=NULL
/*
Object: sp_list_trace
Description: Lists the currently running traces.
Usage: sp_list_trace @TraceId -- the ID
number of a previously started trace (optional)
Returns: (None)
$Author: Ken Henderson $. Email: khen@khen.com
$Revision: 2.0 $
Example: EXEC sp_list_trace -- Lists the currently running traces
Created: 1999-04-01. $Modtime: 2000-12-16 $.
*/
AS
SET NOCOUNT ON
IF @TraceId='/?' GOTO Help
DECLARE @T int
SET @T=CAST(@TraceId AS int)
IF (OBJECT_ID('tempdb..TraceQueue') IS NOT NULL) BEGIN
IF (@T IS NULL) BEGIN
DECLARE tc CURSOR FOR SELECT * FROM tempdb..TraceQueue
FOR READ ONLY
DECLARE @tid int, @tname varchar(20), @tfile sysname
OPEN tc
FETCH tc INTO @tid, @tname, @tfile
IF @@ROWCOUNT<>0 BEGIN
WHILE @@FETCH_STATUS=0 BEGIN
SELECT TraceId, TraceName, TraceFile
FROM tempdb..TraceQueue
WHERE TraceId=@tid
SELECT * FROM ::fn_trace_getinfo(@tid)
FETCH tc INTO @tid, @tname, @tfile
END
END ELSE PRINT 'No traces in the trace queue.'
CLOSE tc
DEALLOCATE tc
END ELSE BEGIN
SELECT TraceId, TraceName, TraceFile FROM tempdb..TraceQueue
WHERE TraceId=@T
SELECT * FROM ::fn_trace_getinfo(@T)
END
END ELSE PRINT 'No traces to list.'
RETURN 0
Help:
EXEC sp_usage @objectname='sp_
list_trace',@desc='Lists the currently running traces.',
@parameters='@TraceId -- the ID number
of a previously started trace (optional)',
@author='Ken Henderson', @email='khen@khen.com',
@version='2', @revision='0',
@datecreated='19990401', @datelastchanged='20001216',
@example='EXEC sp_list_trace -- Lists the currently running traces'
RETURN -1
GO
exec sp_list_trace
(Results)
TraceId TraceName TraceFile
----------- -------------------- ----------------
4 tsqltrace c:\TEMP\tsqltrace_20001204012356767
traceid property value
----------- ----------- ------------------------
4 1 2
4 2 c:\TEMP\tsqltrace_20001204012356767
4 3 5
4 4 NULL
4 5 1
Sp_list_trace calls the ::fn_trace_getinfo() system function to access key info for each trace. The table in Listing 21-6 with traceid as its first column is produced by ::fn_trace_getinfo(). You can take the trace name listed in the top table (which comes from Tempdb..TraceQueue) and pass it to sp_stop_trace to stop the trace if you like.
These routines present a viable alternative to the Profiler utility and may come in handy in your work. It wouldn't be difficult to combine the three of them into one master procedure so that you wouldn't have three procedures to keep track of. Again, this is an exercise best left to the reader.
