: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)
       Select 1
       Select 0

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