Introduction

There are several undocumented system stored procedures and extended stored procedures in SQL Server 2005 which can be of enormous use. Since they are undocumented, it can be risky to rely upon them. There is always the possibility that they will be dropped or altered, which can be worse, with the next version of SQL Server or even the next service pack. If it is important that the code in question works with the next version of SQL Server then it is wise to avoid undocumented procedures. But they can be more than worth that small risk when compatibility with future versions is not a priority.

XP_FileExist

The usage is:

EXEC xp_fileexist <filename> [, <file_exists INT> OUTPUT]

If executed without providing an output variable, it will display 3 columns indicating whether the file passed in exists, whether it is a directory, and if the parent directory exists. For instance:

exec master.dbo.xp_fileexist 'C:\temp'

Results in this output:

If it is executed with the output parameter it will set the value to one if the file exists and 0 otherwise. It will set the variable to 0 if the name given is a directory.

Generally, it is better to check validate the existence of any files prior to calling the T-SQL script, but there are certainly times when it is convenient to be able to do it from within a T-SQL script. This syntax is far more graceful than work arounds which can be achieved with xp_cmdshell.

SP_MSForEachDb

sp_msforeachdb will execute the attached string as a command against every database on the server. Any question marks (?), within the string will be replaced by every database name. It can be useful for finding the database which holds a given table on a server. For example:

exec dbo.sp_MSforeachdb 'select ''?'', * from [?].INFORMATION_SCHEMA.TABLES where table_name like ''authors'' '

will find every table named authors on the server. It can also be useful for running maintenance routines against every database. For instance, this:

exec dbo.sp_MSforeachdb 'DBCC SHRINKDATABASE(N''?'' )'

will shrink the file size of every database on the server. It may be worth noting that on a well designed production system it would not be a good idea to run that command, but it can be useful for reclaiming file space on test and development servers and illustrates the broader ways it can be used for administrative commands when needed.

SP_MSForEachTable

sp_msforeachtable is very similar to sp_msforeachdb, except executing the command against every table in the database. For instance, if there is a test database and all tables in the test database need to emptied while retaining the table structures, this command could be used:

exec dbo.sp_msforeachtable 'delete test.dbo.[?]'

SP_who2

Sp_who2 is like sp_who's bigger brother. It functions in a very similar fashion to the documented procedure sp_who except that some of the column names are slightly changed, the ecid column is dropped, and some additional columns are listed. It adds the cputime, diskio, lastbatch, and programname columns to the information provided by sp_who. Its output appears as:

Both sp_who and sp_who2 with its added columns can be tremendously valuable in scripts and programs, but for use on an ad hoc basis it is often more convenient to invoke the Activity Monitor GUI.

sp_MSdependencies

sp_MSdependencies is a powerful procedure that can be used to determine all dependencies for an object within a database. Its usage is:

exec dbo.sp_MSdependencies [<object name> ] , [<object type>], [<Flags>], [<objlist>]

The procedure also accepts an integer variable @intrans, but it does nothing. All of the variables are optional and in most cases it is not necessary to pass both an object name and an object type. If

exec sp_msdependencies '?'

is executed it will provide a brief description of the usage options.

sp_MSdependencies is similar to the documented stored procedure sp_depends, but it is simultaneously more flexible and more difficult to use. sp_depends will return two result sets, the first lists those things that the targeted object depends on directly and the second listing those things which depend directly on the targeted object, and in both cases it does not consider foreign key relationships to be dependencies. sp_MSdependencies on the other hand does count foreign key relationships and will return either things which depend on the target or on which the target depends and precisely what it returns is determined by the flags passed. By default, it will not return system tables or other internal objects or user defined data types, but it can be set to do so by using the flags switch. It also formats its results different from sp_depends.

If it is executed with only an object name, it will return everything on which that object depends. For instance:

exec sp_msdependencies 'dbo.titleview'

exected in the pubs database returns

Which indicates that dbo.titleview depends on authors, titles, and titleauthor while authors depends on the publishers table. The oType of 8 indicates that they are all tables.

Providing only an object type by number will result in a list of everything which depends directly or indirectly on any object of that type within the current database. In the sample pubs database, the dbo.titleview is the only view, so if views were selected and

exec sp_msdependencies NULL, 2

were executed the results would be

oType       oObjName                 oOwner                          oSequence
----------- ------------------------ ------------------------------- ---------
8           authors                  dbo                             1
8           publishers               dbo                             1
8           titles                   dbo                             2
8           titleauthor              dbo                             3
4           titleview                dbo                             4

Providing everything which any view depends on, including the view itself.

The flags can be added to either an object name or an object type and they determine precisely what is returned. The flags parameter is a bitmap expressed in hexadecimal. It permits precise control over what types of objects are returned and will allow the return of children, or objects which depend on the target, instead of parents, or objects on which the target depends. Using the flags, it can be set to return the system objects and user defined data types which are normally skipped. It can also be set to return only first level relationships which makes it more like sp_depends. For instance, to retrieve the objects which depend on the titles table execute:

exec sp_msdependencies 'titles', NULL, 0x401fd

which returns:

Everything on the list depends either directly or indirectly on the titles table. For instance, the procedure byroyalty does not directly rely on the titles table, but it relies on titleview which in turn is dependant on the titles table.

In general, it is more convenient and user friendly to simply use the GUI provided in the SSMS to gather the dependencie information when it is needed on an ad hoc basis, but sp_msdepencies can be invaluable in creating scripts in which dependencies need to be evaluated. Its ability to consider foreign key relationships and indirect dependencies can also make it valuable in situations where the documented sp_depends is not appropriate.

Conclusion

Undocumented procedures should always be used cautiously, especially when dealing with procedures that are expected to be able to migrate from one version of SQL server to another. Undocumented procedures are far more likely than their documented counterparts to be changed, and they will not be officially supported if technical support is ever required. With those risks in mind, they can often be tremendously useful. This provides a short list of some of the more useful ones and how they can be used in SQL Server 2005.