How to check if a file exists with xp_fileexist

Question: How can I find if a file exists  on the OS using the sql command language? I’m executing some stored procedures, requiring some checks on files .

Answer: The extended stored procedure xp_fileexist will return some information about whether the file passed through the input parameter exists

exec master.sys.xp_fileexist 'C:\file.txt'

File Exists	File is a Directory	Parent Directory Exists
1	0	1

From a scripting perspective, you may just want to return one column with a value.

An alternative way of executing sp_fileexist is to add an output parameter.This will return one column , which may be easier to consume in the code flow

DECLARE @b_output INT 
exec master.sys.xp_fileexist 'C:\file.txt',@b_output OUTPUT

SELECT @b_output as ‘RESULT’
RESULT
0

Read More

Display all files in a directory with master.sys.xp_dirtree (SQL Server ...

Xp_cmdshell – the most dangerous extended stored procedure

How to create a temporary Stored Procedure using TempDB (SQL ...