Recently, at work we were working on a one-time import of some data files. I needed a list of the files in a table in the database, so that I could loop through them and run a Bulk Insert command. I got the list of files from the command line, but my co-worker showed me a way to do that in T-SQL.
There’s an undocumented extended stored procedure: master.dbo.xp_dirtree

EXEC master.dbo.xp_dirtree 'C:\Temp', 1, 1;

The first parameter is the path you want to examine.
The second parameter is the depth. 0 will show everything, or you can use a number that will return everything at that depth and lower. The depth values start at 1, with 1 being at the level of the path that was passed in. Using 3, for example, will return everything with a depth of 1, 2 or 3.
The third parameter is a bit value. Using 0 will only return directories (including hidden directories), and 1 will return directories and files. If 1 is used, then the result set will include a ‘file’ column, where 1 means a file and 0 a directory.

You can create a table for the results, then use a hack to get the stored procedure results into that table:

DROP TABLE IF EXISTS #Files;

CREATE TABLE #Files (
	[FileName] VARCHAR(250) NOT NULL,
	Depth TINYINT NOT NULL,
	IsFile BIT NOT NULL
);

INSERT INTO #Files
EXEC master.dbo.xp_dirtree 'C:\Temp', 1, 1;

SELECT * FROM #Files;

GO

From there, you can filter out exactly what you need.