List Directory Files using T-SQL xp_cmdShell Stored Procedure

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.