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