List Directory Files using T-SQL xp_cmdShell Stored Procedure
Using SQL xp_cmdShell stored procedure to list directory files and display contents as a file list is possible for Transact-SQL developers by creating a short T-SQL script as shown in this SQL tutorial.
Besides using SQL CLR to list files in directory, T-SQL can be also used by developers for the same task to list files in a directory.
Please note that before using xp_cmdshell extended procedure on a SQL Server instance, it should be enabled on that server. Database administrators and SQL developers can refer to following tutorial for more detail on how to enable xp_cmdshell in SQL Server using sp_configure
Here is example SQL codes showing how to list directory files in a file folder using sql xp_cmdshell procedure.
CREATE TABLE dirList (
id int identity(1,1),
line nvarchar(1000)
)
GO;
INSERT INTO dirList (line) EXEC xp_cmdshell 'dir C:\SQLDatabases'
SELECT * FROM dirList;
WITH CTE AS (
SELECT
id,
SUBSTRING(line,1,17) [date],
SUBSTRING(line,18,19) sizeordir,
SUBSTRING(line,37,100) name
FROM dirList
WHERE id > (
SELECT MIN(id) FROM dirList WHERE line LIKE '%<DIR>%..%'
) AND id < (SELECT MAX(id) - 2 FROM dirList)
)
SELECT
id,
[date],
isDirectory = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 1 ELSE 0 END,
isFile = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 0 ELSE 1 END,
name
FROM cte
Above T-SQL script can be used to list files in directory using SQL code.
The returned isDirectory column and isFile column can be used to distinguish files and folders within the target file folder.
SQL Server xp_cmdshell procedure used to list files and folders in a target file folder
By using recursive SQL Select statements, it is possible to list all files and all folders under the root C drive.
Again recursive SQL statement will enable to get the directory structure of the target file folder or disk drive given as parameter in the T-SQL xp_cmdshell statement.