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

 
How to check if File exists in a directory or not
 
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

 

Check Whether File exists or not

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

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Comments are closed.