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.