How to list files inside a folder with SQL Server

CREATE TABLE #dbspacedetails ( servername varchar(100), dbname varchar(100), physicalFileName varchar(100), FilePath varchar(200), allocated_space_mb decimal(10,2), used_space_mb decimal(10,2), free_space_mb decimal(10,2) )

DECLARE @name VARCHAR(200) DECLARE @path VARCHAR(1000)

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb') and state_desc = 'ONLINE' order by name asc

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
set @path = 'use ['+ @name+']'+char(10)+'insert into #dbspacedetails

select @@SERVERNAME SERVERNAME, db_name() DBName, name AS FileName, Physical_name as FilePath, size/128 AS allocated_space_m, (size/128)-((size/128 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128)) as used_space_mb, size/128 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128 AS free_space_mb from sys.database_files'

   exec(@path)     
    

   FETCH NEXT FROM db_cursor INTO @name   

END

CLOSE db_cursor
DEALLOCATE db_cursor

select servername, dbname, physicalFileName, FilePath, allocated_space_mb, used_space_mb, free_space_mb, CAST (CASE WHEN (((free_space_mb*1.0/(CASE WHEN allocated_space_mb = 0.00 THEN 1 ELSE allocated_space_mb END)*1.0)100.0)) <0 THEN 0 ELSE ((free_space_mb1.0/(CASE WHEN allocated_space_mb = 0.00 THEN 1 ELSE allocated_space_mb END)*1.0)*100.0) END AS INT) as percentfree from #dbspacedetails where FilePath like 'H:%'-- and --dbname = 'CentralDWH' order by free_space_mb desc --order by percentfree desc

drop table #dbspacedetails

/*

--select * from #dbspacedetails ----where FilePath like 'f:\data%' -- order by free_space_mb desc

select , ((free_space_mb1.0/allocated_space_mb*1.0)*100.0) as percentfree from #dbspacedetails --where FilePath like 'E:\DATA01%'-- and --dbname = 'CentralDWH' order by free_space_mb desc -- -- order by percentfree desc

*/