Check for file exists or not in sql server

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

dbo.fn_FileExists(filepath)

DECLARE @isExists INT
exec master.dbo.xp_fileexist 'C:\vishwanath\21776656.docx', 
@isExists OUTPUT
SELECT case @isExists 
when 1 then 'Yes' 
else 'No' 
end as isExists  

Get List Of Files From a Windows Directory to SQL Server

In SQL Server, we read data from single text file; excel file…etc. However we can extend this to read all the files in a particular directory. This post demonstrates a part of this scenario. Here we will discuss how to get the list of files from a particular directory, then can be extended to load them into SQL Server, which is not the scope of this post.

So in order to use xp_cmdshell, we need to enable it as it is disabled by default.

Here is the way to enable:

1 2 3 4 5 6 7 8 9 10 11 12

--allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO --Update the currently configured value for advanced options. RECONFIGURE GO --Enable XP_CMDSHELL EXEC sp_configure 'xp_cmdshell', 1 GO --Update the currently configured value for this feature. RECONFIGURE GO

Refer Image-1 for the list of files in the directory. Now create a table and load the file list into it:

(Image-1)

1 2 3 4 5 6 7 8 9 10 11 12 13

--Create the table to store file list CREATE TABLE myFileList (FileNumber INT IDENTITY,FileName VARCHAR(256)) --Insert file list from directory to SQL Server DECLARE @Path varchar(256) = 'dir C:\Import\' DECLARE @Command varchar(1024) =  @Path + ' /A-D  /B' INSERT INTO myFileList EXEC MASTER.dbo.xp_cmdshell @Command --Check the list SELECT * FROM myFileList GO --Clean up DROP TABLE myFileList GO

Here is the result in the table.