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 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