Cool Tips‎ > ‎File System‎ > ‎

File System - 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

ċ
Check if a file exists.sql
(0k)
Andy Hughes,
Jul 5, 2012, 10:38 AM
Comments