Undocumented SQL Server stored procedures

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.

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 sp...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Stored Procedures SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Stored procedures, triggers and SSRS reports Top tips and tricks for SQL Server database development Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL/Transact SQL (T-SQL) Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server SQL language crash course (just enough to be dangerous) Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility SQL/Transact SQL (T-SQL) Research Database Development Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server Speed up reports in SQL Server Reporting Services with caching Data Transformation Services vs. SSIS: The key differences Working with IntelliSense in SQL Server 2008 Management Studio Top tips and tricks for SQL Server database development Managing the development lifecycle with Visual Studio Team System 2008 Processing XML files with SQL Server functions A first look at Visual Studio Team System 2008 Database Edition How to create a SQL inner join and outer join: Basics to get started New datetime data types in SQL Server 2008 offer flexibility

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary library  (SearchSQLServer.com) trigger  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

ace allocations in two columns: drive (drive letter) and MB free (free space). This does not take any parameters.

EXEC MASTER..XP_FIXEDDRIVES

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!

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. Submit a Tip 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.