Listing Files / Directories (Names) Using T-SQL

/*
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
*/
-- Get file list in specified directory (folder)
-- T-SQL command shell - insert exec
CREATE TABLE #FileList (
  Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir f:\data\'
DECLARE @Command varchar(1024) =  @Path+' /A-D  /B'
PRINT @Command
INSERT #FileList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #FileList WHERE  Line IS NULL

SELECT * FROM   #FileList
GO
DROP TABLE #FileList
GO

-- Get directory (subdirectory, folder) list in specified directory (folder)
CREATE TABLE #DirectoryList (
  Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir f:\data\'
DECLARE @Command varchar(1024) =  @Path+' /A-A  /B'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE  Line IS NULL

SELECT * FROM   #DirectoryList
GO
DROP TABLE #DirectoryList
GO

-- List all files in a directory - T-SQL parse string for date and filename
-- Microsoft SQL Server command shell statement - xp_cmdshell
DECLARE @PathName VARCHAR(256) ,
@CMD VARCHAR(512)

CREATE TABLE #CommandShell ( Line VARCHAR(512))

SET @PathName = 'F:\data\download\microsoft\'

SET @CMD = 'DIR ' + @PathName + ' /TC'

PRINT @CMD -- test & debug
-- DIR F:\data\download\microsoft /TC

-- MSSQL insert exec - insert table from stored procedure execution
INSERT INTO #CommandShell
EXEC MASTER..xp_cmdshell @CMD

-- Delete lines not containing filename
DELETE
FROM #CommandShell
WHERE Line NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR Line LIKE '%<DIR>%'
OR Line is null
-- SQL reverse string function - charindex string function
SELECT
FileName = REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ),
CreateDate = LEFT(Line,10)
FROM #CommandShell
ORDER BY FileName
DROP TABLE #CommandShell

GO
------------