Undocumented SQL Server stored procedures

T-SQL commands should not be used to manipulate the file system for security reasons, but some undocumented SQL Server stored procedures can help you work with directories, files and drives.

A T-SQL script will rarely need to access the file system directly. The vast majority of programming recommendations encourage you to manipulate files or folders through business logic (i.e., using a C#/VB.NET application) rather than a T-SQL script. However, in the few instances where you have little choice but to use T-SQL to work directly with files and folders, there are undocumented system procedures. For instance, if you're creating...

a database installation script that requires the presence of certain folders, it helps to have a way to check for those folders within the context of the T-SQL script itself when needed.

The following is a list of undocumented stored procedures that enable limited work with the file system.

1. xp_dirtree: Returns a list of all subdirectories for a given directory in a column named subdirectory and a column named depth, which describes how many directory levels down the given directory is.

EXEC MASTER..XP_DIRTREE 'C:\myfolder'

2. xp_fileexist: Checks to see if a given file exists or not. It returns three columns with a value of 1 (yes) or 0 (no): File Exists, File is a Directory and Parent Directory Exists.

EXEC MASTER..XP_FILEEXIST 'C:\myfile.text'

3. xp_fixeddrives: Returns a list of all fixed hard drives with free space allocations in two columns: drive (drive letter) and MB free (free space). This does not take any parameters.

EXEC MASTER..XP_FIXEDDRIVES

4. xp_subdirs: Delivers a list of subdirectories, if there are any, for the given directory. This does not recursively list subdirectories; it only lists whatever subdirectories are immediately found within the stated directory. The returned table has one column, subdirectory.

EXEC MASTER..XP_SUBDIRS 'C:\mydir'

Final notes

None of these commands allow you to actually manipulate the file system.

Get more on SQL Server development:

 The reason for this should be obvious: T-SQL scripts are not the best place, neither programmatically nor with regard to security, to allow such things to be done. In theory you could use the xp_cmdshell stored procedure to execute a shell command, but it should only be done if there are absolutely no other options.

Also, these commands may not turn up in future versions of SQL Server, but are confirmed to work in SQL Server 7.0 and 2000.

ABOUT THE AUTHOR
Serdar Yegulalp has been writing about computers and information technology for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.

This was first published in September 2005

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close