Read List Of Files From Folder In SQL Server

Reading the list of files from the drive’s folder path was one of the tasks that I wanted to achieve for desired results at a faster pace. While it’s not feasible to open the remote server every time and check on a specific drive’s path to fetch the files. One possible workaround to achieve was to use the xp_cmdshell to get the results in the rows of text.

Xp_cmdshell

The xp_cmdshell is a windows shell command wherein desired strings can be passed for execution in SQL Server. 

General Syntax of xp_cmdshell:

xp_cmdshell { 'command_string' }

Example of xp_cmdshell:

xp_cmdshell 'dir d:\ /b'

Read List Of Files From Folder SQL Server

Code to read the list of files from the folder:

declare @files table (ID int IDENTITY(1,1), FileName varchar(100))
insert into @files
exec xp_cmdshell 'dir d:\ /b'
select * from @files
--where Filename like '%your file name%' -–please uncomment if needed to filter the files
--please use the WHERE condition only to filtering, else please use without WHERE clause

Read List Of Files SQL Server 2







The code can help you fetch the files from directory or path, and the same can be used for any functionality integration as per your requirement.

0 comments: