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 throu 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 filename with extension as a
result of “dir” command
