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'