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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.