How to check if a file exists with xp_fileexist

07 January,2017 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to check if a file exists with xp_fileexist


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer