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.
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'
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'
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
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.
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 is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
