Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to obtain a list of files and directories (subfolders) in the specified directory.

-- Get file list in specified folder (directory) - dynamic command 
-- T-SQL list files - MSSQL insert exec - T-SQL commmand shell - xp_cmdshell
CREATE TABLE #FileList (
  Line VARCHAR(512))
DECLARE @CMD nvarchar(256)
SET @CMD = 'dir f:\data\ /A-D  /B'
INSERT #FileList
EXEC MASTER.dbo.xp_cmdshell @CMD
 
DELETE #FileList
WHERE  Line IS NULL
 
SELECT *
FROM   #FileList
GO
 
-- Get subfolder (subdirectory) list in specified folder
-- T-SQL list directories - create temporary table
CREATE TABLE #DirectoryList (
  Line VARCHAR(512))
DECLARE @CMD nvarchar(256)
SET @CMD = 'dir f:\data\ /A-A  /B'
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @CMD
 
DELETE #DirectoryList
WHERE  Line IS NULL
 
SELECT *
FROM   #DirectoryList
GO

Related articles:

How to list all files in a directory?

List filenames from a directory into a table from SP.

Import multiple Files to SQL Server using T-SQL