TSQL - How to list files inside a folder with SQL Server

In this section, we are going to list all the files within the folder or directory by using T-SQL. This is very common scenario if you have SSIS environment to load the multiple files from the shared location. In absence of  SSIS or other ETL tools,  you have to write T-SQL codes to list down all the files in the file directory or folder before load their data into the system one by one. 

Database-level configuration options - Before using this code, we should change the global configuration settings for the current server as given below -

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

T-SQL Code through xp_DirTree command - After doing the needful database level configuration setting, we are able to run the below code to list all the files from the shared folder as given below –

----- Table Variable to Store the file List declare @TotalFiles Table(SubDirectory Varchar(200), Depth Int, Files Int, ictr int primary key identity(1,1)) ----- Folder or Directory Path Variable declare @inpFilePath varchar(200)='C:\Sample Data Pack\'; ---- Insert Values into Table variable INSERT INTO  @TotalFiles (SubDirectory, Depth , Files ) EXEC Master.dbo.xp_DirTree @inpFilePath,1,1

T-SQL Code through XP_cmdshell command - XP_cmdshell is the other option to list all the files from the shared folder but it requires more data filters as given below –

----- Table Variable to Store the file List declare @TotalFiles Table(SubDirectory Varchar(200), Depth Int, Files Int, ictr int primary key identity(1,1)) ----- Folder or Directory Path Variable declare @inpFilePath varchar(200)='E:\Sample Data Pack\'; ---- Set Dirctory command before folder path SET @inpFilePath= 'DIR '+ ' "' + @inpFilePath +'"' ---- Insert Values into Table variable INSERT INTO  @TotalFiles (SubDirectory) EXEC Master.dbo.XP_cmdshell @inpFilePath SELECT FilesName=Substring(SubDirectory,40,100) FROM @TotalFiles ----- Remove all directories where SubDirectory NOT Like '%<DIR>%' ----- remove bytes AND SubDirectory NOT Like '%bytes%' ---- file name will start from 40 AND LEN(SubDirectory)>40 ---- file name must have . extenion AND SubDirectory Like '%.%'