:xp_fileexists is a very useful undocumented stored procedure of SQL Server.

The usage of xp_fileexist is as follow

Exec xp_fileexist “E:\abc.txt”

The values returned are:

You can also use OUTPUT parameter to get the value of “File Exists” column as below:

Declare @vFileExists int
 
exec master.dbo.xp_fileexist 'E:\abc.txt', @vFileExists OUTPUT
 
Select @vFileExists

If you want to save all three returned values, then you will have to go through Temp Table approach:

Declare @vFileExists Table (FileExists int, FileDir int, ParentDirExists int)
 
insert into @vFileExists
 
       exec master.dbo.xp_fileexist 'E:\abc.txt'
 
Select * from @vFileExists

Sometimes xp_fileexist behaves abnormally. Your file is there and it does not check the file and returns 0 in FileExists column. This is an extended SP so its behavior may also change with different versions of SQL Server.

If you are facing the same problem, change you code with xp_cmdshell “dir” approach:

Declare @vExistsPath nvarchar(100)
 
Declare @files Table ([FileName] nvarchar(100))
 
Set @vExistsPath = ''
 
Set @vExistsPath = 'E:\abc.txt'
 
Set @vExistsPath = 'dir ' + @vExistsPath + ' /b'
 
Insert into @files EXEC xp_cmdshell @vExistsPath
 
Select * from @files
 
if Exists(Select 1 from @files where [FileName] = 'abc.txt' And [FileName] is Not Null)
 
begin
 
       Select 1
 
end
 
else
 
begin
 
       Select 0
 
end

The “/b” switch returns only filenames with extension as a result of “dir” command.