- String Functions
- Cursor Functions
- Security Functions
- Profiler-Specific Functions
- Rowset Functions
- Other Functions
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Profiler-Specific Functions
The other set of functions classified as security functions are specific to Profiler traces. If you're not familiar with a SQL Server Profiler and you're aspiring to be a DBA, you should definitely learn how to use this tool. In simple terms the Profiler helps you monitor the activity on your server by capturing SQL statements as they are executed. In addition, the Profiler can be used to capture logon events, monitor performance, and more. You can use the Profiler to create traces; alternatively, you can use system procedures and extended procedures to do the same.
SQL Server 2000 provides numerous functions that start with fn_. These are system-supplied UDFs, which can be used for troubleshooting or monitoring your system. Don't bother trying to read the code of these functions—SQL Server won't let you. Security functions specific to Profiler are such system-supplied UDFs.
Notice that system-supplied UDFs are executed slightly differently from typical UDFs and system functions. You must use a colon (:) prior to specifying the name of the system-supplied UDF, as in the following:
SELECT * FROM :: fn_system_function
Perhaps the most useful Profiler function for beginners is fn_trace_gettable. If you have a trace file saved on a hard drive, you can bring the results into the Query Analyzer using this function. For example, the following query returns a few columns from a trace file e:\my_trace.trc:
SELECT TextData, NTUserName, ClientProcessID, ApplicationName
FROM ::fn_trace_gettable('e:\my_race.trc', default)
Results (abbreviated):
|
TextData |
NTUserName |
ClientProcessID |
ApplicationName |
|
SET NOEXEC OFF SET PARSEONLY OFF SET ROWCOUNT 0 |
Administrator |
1720 |
SQL Query Analyzer |
|
DBCC USEROPTIONS |
Administrator |
1720 |
SQL Query Analyzer |
|
master.dbo.sp_Mshasdbaccess |
Administrator |
1720 |
SQL Query Analyzer |
|
set showplan_text off |
Administrator |
1720 |
SQL Query Analyzer |
|
SET NOEXEC OFF SET PARSEONLY OFF |
Administrator |
1720 |
SQL Query Analyzer |
|
set showplan_all off |
Administrator |
1720 |
SQL Query Analyzer |
|
use [master] |
Administrator |
1720 |
SQL Query Analyzer |
|
SELECT COUNT(*) FROM sysperfinfo |
Administrator |
1720 |
SQL Query Analyzer |
|
SELECT * FROM sysperfinfo |
Administrator |
1720 |
SQL Query Analyzer |
|
SELECT * FROM sysperfinfo |
Administrator |
1720 |
SQL Query Analyzer |
|
SELECT name FROM sysobjects WHERE type = 'u' ORDER BY 1 |
Administrator |
1720 |
SQL Query Analyzer |
|
SELECT COUNT(*) FROM spt_monitor |
Administrator |
1720 |
SQL Query Analyzer |
If there is enough interest, the rest of the Profiler-related functions will be discussed in a future article.