List Directory Files using T-SQL xp_cmdShell Stored Procedure
In this sql tutorial, developers can find a short t-sql script which can be used to list directory files and display contents as a file list using the t-sql xp_cmdShell stored procedure.
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.
Here is a example code 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
The above t-sql script can be used to list files in directory using sql code.
The isDirectory column and isFile column can be used to distinguish files and folders within the 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.
|