T-SQL Query to get the list of files in a folder

Here is a T-SQL Query to list all the files in a folder. This uses a undocumented extended stored procedure to get the details.

DECLARE @Path nvarchar(500) = 'E:\Test' --Change the path

DECLARE @FindFile TABLE 
 (FileNames nvarchar(500)
  ,depth int
  ,isFile int)

INSERT INTO @FindFile 
EXEC xp_DirTree @Path,1,1

SELECT FileNames from @FindFile where isFile=1