Undocumented extended stored procedure - xp_fileexist

This post was originally posted ni this blog entry

It's quite shocking to hear the word "undocumented" in SQL Server. This simply means that you can break your neck trying to find these topics in both the Books Online or Microsoft.com and you won't find anything. But there's always Google to take your chances. As I was trying to search for a VBScript to check for an existence of a file in a file system (it would be a lot easier in .NET but I'm limited to what I just have) when I chanced upon this extended stored procedure - xp_fileexist. DBAs do file system checks whenever they do data transformation or data loading from a delimited text file, an Excel spreadsheet or anything similar. If this was done by a batch job you need to check first whether a file exists based on a specific filename format (such as Test_yyyymmdd.xls). You can create a simple stored procedure to check for the formatting of the filename but to check whether the file exists before you to do your task is not something that you can do with SQL Server. Good thing there is such a stored procedure - xp_fileexist. This verifies whether a specified file exist. This stored procedure is located in the master database where you can execute it in the following syntax:

xp_fileexist 'C:\autoexec.bat'

This will return three columns indicating if the file exists, if the file is a directory and if the file's parent directory exists as a result set. You may want to include this in a script to do functional checks such as the example below

DECLARE @result INT
EXEC xp_fileexist 'c:\autoexec.bat', @result OUTPUT
IF (@result = 1) --@result will return 1 if the specified file exists, if it doesn't, it will return 0
PRINT 'file exists'
ELSE
PRINT 'file does NOT exist'

This is also helpful when you want to check for the existence of a database backup file as it has been recorded in the msdb database. One of the challenges I have is checking if a database transaction log backup still exists if a log shipping job starts failing.

Just remember that when using this stored procedure is a bit risky as Microsoft reserves the right to change or even remove this in future versions or even service packs without having any documentation (well, it really doesn't have any after all) so make sure to test your scripts after applying service packs or upgrading.