Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

SQL File Results

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.

SQL Results

Using xp_cmdshell, DOS commands such as DIR can be used to get data and manipulate it in SQL Server.