SQL Server: How to Check if a File Exists in a Directory

Frequently, when working with SQL, we need to know if a file exists in a local directory or not. This can be done using SQL Server’s built-in procedure known as master.dbo.xp_fileexist. This user-defined function (UDF) checks whether or not a file exists in a specified directory.

create FUNCTION dbo.fc_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

In the function above, we are passing parameter @path, and the built-in procedure master.dbo.xp_fileexist will check whether that file really exists on the specified path. The function will return 1 if it exists. If the file does not exist, the function will return 0.

Let us see how this function performs with an example.

select dbo.fc_FileExists('C:\mywork\tech-recipes.rar');
-----
1


select dbo.fc_FileExists('C:\mywork\barfi.mp3');
-----
0

If you’ve a table with a column listing all the file paths you can use this function on table too.

Create table filelist
(
  fileno int,
  filename varchar(max)
);

Let us insert sample data.

Insert into filelist values (1, 'C:\mywork\tech-recipes.rar');
Insert into filelist VALUES (2, 'C:\mywork\barfi.mp3');

Here we can use the dbo.fc_FileExists(filename) function to check whether or not the file exists.

Select fileno,
       filename, 
       dbo.fc_FileExists(filename) as IsFileExists
From   filelist;

fileno      filename                              IsFileExists
----------- ------------------------------------  ------------
1           C:\mywork\tech-recipes.rar                  1    
2           C:\mywork\barfi.mp3                         0

IsFileExists is a temporary column. If you want to make it a permanent column, you will need to use update query.