check if file exists in sql server

We all need to check some times if file exists on physical drive during import export or restore of database. May be for any other reason, so here are some old methods to check if file exists on local drive. The scripts were written in SQL Server 2000 but should work on SQL Server 2005.

-- Check file exists using the scripting object
DECLARE @Path varchar(128) ,
 @FileName varchar(128)
 SET @Path = 'C:\'
 SET @FileName = 'FILE_NAME.EXT'

DECLARE @objFSys int
DECLARE @i int
DECLARE @File varchar(1000)
 
 SET @File = @Path + @FileName
 EXEC sp_OACreate 'Scripting.FileSystemObject', @objFSys out
 EXEC sp_OAMethod @objFSys, 'FileExists', @i out, @File
 IF @i = 1
  PRINT 'file exists'
 ELSE
  PRINT 'file does not exists'
 EXEC sp_OADestroy @objFSys
 

The second method

-- Check if file exists using xp_cmdshell
DECLARE @Path varchar(128) ,
 @FileName varchar(128)
 SET @Path = 'C:\'
 SET @FileName = 'FILE_NAME.EXT'

DECLARE @cmd varchar(1000)
 CREATE TABLE #tmp(s varchar(1000))
 
 SET @cmd = 'dir /B ' + @Path + @FileName
 INSERT #tmp EXEC master.dbo.xp_cmdshell @cmd
 
 IF EXISTS(SELECT * FROM #tmp WHERE s = @FileName)
  PRINT 'file exists'
 ELSE
  PRINT 'file does not exists'
 DROP TABLE #tmp

The third method

-- last check file exists using xp_fileexists
DECLARE @Path varchar(128) ,
 @FileName varchar(128)
 SET @Path = 'C:\'
 SET @FileName = 'FILE_NAME.EXT'

DECLARE @i int
DECLARE @File varchar(1000)
 
 SET @File = @Path + @FileName
 EXEC master..xp_fileexist @File, @i out
 IF @i = 1
  PRINT 'file exists'
 ELSE
  PRINT 'file does not exists'



 4/19/2009

Ranjith wrote on 5/1/2009

Superb Description man. Thanks a lot.

Wimpie wrote on 6/24/2009

exactly what i was looking for... in triplicate :D

mydim wrote on 7/1/2009
apbg.com.ua

thats is better in cmd shell: if exist {FilePath} echo 1


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.