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

Posted September 20, 2012 by Vishwanath Dalvi in Database, SQL Server

Last Updated on

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))
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)

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');

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

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