Problem solve Get help with specific problems with your technologies, process and projects.

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.


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.


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.


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.


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.

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 last published in September 2005

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

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.