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))
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.
About Vishwanath Dalvi
View more articles by Vishwanath Dalvi
The Conversation
Follow the reactions below and share your own thoughts.
Comments are closed.