Author:    ChillyDBA

Date:        5 Jul 2012

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.

 

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