Reader to Reader: Undocumented Stored Procedures

An underused feature of SQL Server

Extended stored procedures are a useful way to display information about files or your system. Extended stored procedures let you execute noninteractive DLLs from a stored procedure or an Interactive SQL (ISQL) window. SQL Server Books Online (BOL) documents about 90 extended stored procedures in SQL Server 7.0 and 20 in 6.5. More than 145 documented and 50 undocumented extended stored procedures ship with SQL Server. For a list of all stored procedures, run

EXEC sp_helpextendedproc

Some of the procedures in this list, such as xp_enumgroups, won't run if you're using the SQL Server Desktop edition on Windows 98, because there aren't any NT groups to report.

One of the least-used undocumented stored procedures is xp_cmdshell, which lets you execute any OS command via Transact SQL (T-SQL). For example, to notify users that SQL Server will stop, you simply run the command:

EXEC master..xp_cmdshell 'net send testSystem will shut down in 30 seconds',no_outputWAITFOR DELAY '00:00:30'EXEC master..xp_cmdshell'mssql7binnrecycleserver.bat'/* recycleserver.bat contains a file thatexecutes a net stop command to themssqlserver service.*/

The no_output parameter following the procedure makes the results of the net send command invisible to the ISQL window.

Any xp_enum procedure with no parameters returns a recordset of ODBC settings or user information. Xp_enumdsn with no parameters displays a list and description of all data source names (DSNs) connected to SQL Server. Xp_enumgroups displays all the Windows NT domain groups and descriptions.

Loading extended stored procedures into a temporary table to review later can simplify your role in automating tasks such as monitoring tables or hard- drive growth. Xp_fixeddrives with no parameters displays all the physical hard drives and each drive's amount of free space. You can also use this extended stored procedure to monitor growth. Then you can use the xp_sendmail procedure to send you email when your hard drive capacity reaches a given point.

In SQL Server 7.0, you can use xp_fileexist c:autoexec.bat to return a 0 (not exists) or 1 (exists). For example, you can create a job that executes xp_fileexist every half hour, then if the file exists, directs SQL Server to fire a Data Transformation Services (DTS) package. You need to use quotes around the file name for the xp_fileexist query to work.

A procedure you might want to incorporate in your user-defined stored procedure is xp_getnetname. This procedure returns the WINS name of the server that you're connected to.

These are only some of the undocumented extended stored procedures you can use with SQL Server. Extended stored procedures are an unexplored area of SQL Server that you can customize with C++ components. I predict that in the future, many vendors will come up with solutions for custom components to load into SQL Server.