T-SQL Query to List Files
SQL Query Using xp_cmdshell to List Files in a Directory
This tutorial shows how to generate a list of files in a directory\folder into a table.
Step 1: Enable xp_cmdshell on SQL Server
If not xp_cmdshell is not already configured, open SSMS and type in the code below
sp_configure
'xp_cmdshell',1
go
reconfigure
go
Step 2: Write T-SQL with xp_cmdshell
Create code using a temp table and 2 variables, one for the folder name, the other to capture a DOS command using the DIR command to get the file listing. An Insert Execute on the command name into the temp table will capture the data. Finally, issue a SELECT Statement to view the data from the temp table.
Declare @dir varchar(100)
Declare @folderPath varchar(50)
CREATE TABLE #tmpDir
(ID int IDENTITY(1,1),
fName varchar(400))
SET @folderPath = 'C:\Data'
SET @dir = 'DIR ' + '"' + @folderPath + '"'
print @dir
INSERT #tmpDir EXECUTE master.dbo.xp_cmdshell @dir
SELECT ID, fName FROM #tmpDir
DROP TABLE #tmpDir
The raw data includes some unwanted items such as dates, file sizes, and folders.
Step 3: Create Code to Retrieve File Names Only
Add some additional statements such as Substring with some criteria in the Where clause to get file names only.
DELETE FROM #tmpDir where ID < 6
SELECT SUBSTRING(fileName,40,100) fileName2
FROM #tmpDir
WHERE Name not like '%<DIR>%' and Name not like '%bytes%'
and Name is not null
The results in the SQL Query are exactly what we wanted, a list of the files in a folder.
Using xp_cmdshell, DOS commands such as DIR can be used to get data and manipulate it in SQL Server.