Check if File Exists Using TSQL

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 5 Jul 2012

Description

Utilizes the extended procedure xp_fileexists to determine whether a file exists or not using TSQL

This XP produces a single row result set with 3 columns, only one of which applies in this case. Unfortunately, the only way I can see of consuming this result set at the moment is by loading into a temp table - a crude but effective method.

Code

SET NOCOUNT ON

DECLARE @FileName VARCHAR(255)

DECLARE @Results TABLE

(

[File Exists] INT,

[File is a Directory] INT,

[Parent Directory Exists] INT

)

SELECT @FileName = 'C:\Backup\readme.txt'

INSERT @Results

EXEC MASTER..xp_fileexist @FileName

IF EXISTS (SELECT 1 FROM @Results WHERE [File Exists] = 1)

PRINT 'File Found'

ELSE

PRINT 'File Not Found'

SET NOCOUNT OFF