xp_fileexist and its alternate
: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.