You can use xp_dirtree

It takes three parameters:

Path of a Root Directory, Depth up to which you want to get files and folders and the last one is for showing folders only or both folders and files.

EXAMPLE: EXEC xp_dirtree 'C:\', 2, 1

Can be done using xp_DirTree, then looping through to generate a full file path if required.

Here is an extract of a script I use to restore databases to a test server automatically. It scans a folder and all subfolders for any backup files, then returns the full path.

DECLARE @BackupDirectory SYSNAME = @BackupFolder

  IF OBJECT_ID('tempdb..#DirTree') IS NOT NULL
    DROP TABLE #DirTree

  CREATE TABLE #DirTree (
    Id int identity(1,1),
    SubDirectory nvarchar(255),
    Depth smallint,
    FileFlag bit,
    ParentDirectoryID int
   )

   INSERT INTO #DirTree (SubDirectory, Depth, FileFlag)
   EXEC master..xp_dirtree @BackupDirectory, 10, 1

   UPDATE #DirTree
   SET ParentDirectoryID = (
    SELECT MAX(Id) FROM #DirTree d2
    WHERE Depth = d.Depth - 1 AND d2.Id < d.Id
   )
   FROM #DirTree d

  DECLARE 
    @ID INT,
    @BackupFile VARCHAR(MAX),
    @Depth TINYINT,
    @FileFlag BIT,
    @ParentDirectoryID INT,
    @wkSubParentDirectoryID INT,
    @wkSubDirectory VARCHAR(MAX)

  DECLARE @BackupFiles TABLE
  (
    FileNamePath VARCHAR(MAX),
    TransLogFlag BIT,
    BackupFile VARCHAR(MAX),    
    DatabaseName VARCHAR(MAX)
  )

  DECLARE FileCursor CURSOR LOCAL FORWARD_ONLY FOR
  SELECT * FROM #DirTree WHERE FileFlag = 1

  OPEN FileCursor
  FETCH NEXT FROM FileCursor INTO 
    @ID,
    @BackupFile,
    @Depth,
    @FileFlag,
    @ParentDirectoryID  

  SET @wkSubParentDirectoryID = @ParentDirectoryID

  WHILE @@FETCH_STATUS = 0
  BEGIN
    --loop to generate path in reverse, starting with backup file then prefixing subfolders in a loop
    WHILE @wkSubParentDirectoryID IS NOT NULL
    BEGIN
      SELECT @wkSubDirectory = SubDirectory, @wkSubParentDirectoryID = ParentDirectoryID 
      FROM #DirTree 
      WHERE ID = @wkSubParentDirectoryID

      SELECT @BackupFile = @wkSubDirectory + '\' + @BackupFile
    END

    --no more subfolders in loop so now prefix the root backup folder
    SELECT @BackupFile = @BackupDirectory + @BackupFile

    --put backupfile into a table and then later work out which ones are log and full backups  
    INSERT INTO @BackupFiles (FileNamePath) VALUES(@BackupFile)

    FETCH NEXT FROM FileCursor INTO 
      @ID,
      @BackupFile,
      @Depth,
      @FileFlag,
      @ParentDirectoryID 

    SET @wkSubParentDirectoryID = @ParentDirectoryID      
  END

  CLOSE FileCursor
  DEALLOCATE FileCursor

To avoid giving the sysadmin permissions that xp_dirtree requires, do the following instead:

SQLCLR

Create a SQLCLR assembly with external access permission that returns the list of files as a result set. There are many examples of how to do this.

Here's one that creates the SQLCLR using pure SQL; it's by Jonathan Kehayias. Full explanation.

(Once installed, call it like this: SELECT * FROM master.dbo.os_directory_info('C:\', default) )

SQL Script to create a directory-listing function

/*
-- To uninstall:
USE [master]
GO
DROP FUNCTION [dbo].[os_directory_info]
DROP ASSEMBLY SQLCLRNet_DirectoryBrowser
DROP USER SQLCLRNet_ExampleLogin
DROP LOGIN SQLCLRNet_ExampleLogin 
DROP ASYMMETRIC KEY SQLCLRNet_ExampleKey
GO
*/

/*
* This script creates a function that lists the contents of the given directory.
* It uses a .NET CLR instead of the unsecure option of using xp_cmdshell or xp_dirtree which require sysadmin priveleges.
* It is the handywork of Jonathan Kehayias. You can find the complete explanation and source code here: https://www.sqlservercentral.com/articles/trading-in-xp_cmdshell-for-sqlclr-part-1-list-directory-contents
* 
* Once installed, call the function using something like:
*     SELECT *
*     FROM master.dbo.os_directory_info('C:\', default)
*/

-- Enable Common Language Runtimes (.NET code plugins)
-- (Microsoft Docs on this: https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/clr-integration-enabling?view=sql-server-ver15)
EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO 

USE [master]
GO

/****** Object:  SqlAssembly [SQLCLRNet_DirectoryBrowser]    Script Date: 01/23/2009 22:19:49 ******/
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SQLCLRNet_DirectoryBrowser')
DROP ASSEMBLY [SQLCLRNet_DirectoryBrowser]

GO

/****** Object:  SqlAssembly [SQLCLRNet_DirectoryBrowser]    Script Date: 01/23/2009 22:19:49 ******/
CREATE ASSEMBLY [SQLCLRNet_DirectoryBrowser]
AUTHORIZATION [dbo]
-- Hexadecimal representation of Precompiled Binary below. (Source code here: https://www.sqlservercentral.com/articles/trading-in-xp_cmdshell-for-sqlclr-part-1-list-directory-contents)
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103008C887A490000000000000000E00002210B0108000010000000080000000000000E2F000000200000004000000000400000200000000200000400000000000000040000000000000000800000000200001B020100030040850000100000100000000010000010000000000000100000000000000000000000C02E00004B000000004000006004000000000000000000000000000000000000006000000C000000E02D00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000140F0000002000000010000000020000000000000000000000000000200000602E7273726300000060040000004000000006000000120000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200000000000000000000000000000000F02E0000000000004800000002000500642200007C0B00000900000000000000000000000000000050200000800000000000000000000000000000000000000000000000000000000000000000000000897E69923F70A4BADA0B6E1E1BC164F4F71B7C6FFC97C8DA03DAA4AD965FC428DA61F34B05E1DA6E73AC47D2B64C10791C9945AD4A8AD582286E71B6AB8D9215417732F17E43650EBB0C3B31ABE323C7E627DE10E021072588858B456BF9293EFC2A9D5BDC719A0ED62237A9075402DE93B44C3A99AA3396E7D670E39B875079133002004D00000001000011000F00281000000A731100000A0A0F01281200000A2D160F01281000000A7E1300000A281400000A16FE012B01160C082D09066F1500000A0B2B10060F01281000000A6F1600000A0B2B00072A0000001330020024010000020000110002751A00000114FE0316FE010C083A8D0000000002741A0000010A03066F1700000A281800000A8103000001047E1900000A810400000105066F1A00000A281B00000A81050000010E04066F1C00000A281D00000A81060000010E05066F1E00000A281D00000A81060000010E06066F1F00000A281D00000A81060000010E07066F2000000A8C1C0000016F2100000A281800000A8103000001003882000000000274190000010B03076F1700000A281800000A8103000001047E2200000A8104000001057E2300000A81050000010E04076F1C00000A281D00000A81060000010E05076F1E00000A281D00000A81060000010E06076F1F00000A281D00000A81060000010E07076F2000000A8C1C0000016F2100000A281800000A8103000001002A1E02282400000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000D8020000237E0000440300007C04000023537472696E677300000000C00700000800000023555300C8070000100000002347554944000000D8070000A403000023426C6F620000000000000002000001471502000900000000FA013300160000010000001C00000002000000030000000A000000240000000C00000002000000010000000200000000000A0001000000000006004E0047000600680055000A00950080000A00B10080000A00BC0080000A00C50080000600280109010600940182010600AB0182010600C80182010600E701820106000002820106001902820106003402820106004F02820106006802090106007C0282010600A80295024B00BC0200000600EB02CB0206000B03CB020A005F034403060088037E030600A10347000600BA037E030600DC037E0306000B04470006004A047E0300000000010000000000010001000100100029000000050001000100D0200000000096009F000A0001002C21000000009100D100130003005C22000000008618EB002D000B0000000100F10000000200F60000000100FD00020002000101020003003501020004004201020005004701020006005601020007006801020008007B013900EB002D004100EB0031004900EB0031005100EB0031005900EB0031006100EB0031006900EB0031007100EB0031007900EB0031008100EB0036008900EB0031009100EB003B00A100EB004100A900EB002D00B100EB002D00190074039E02B900EB00310019009603A202C100A803A602C100AE03A902B900C903AF02B900C903B502C900E5039E021900EE03C4022100FA03CA02D1000004CE022900EE03D202C9001404D8023100EE03DD02C9002504D802C9003704D802C9005904E402090068049E0221007104CA0229007604E9020900EB002D0020007B00E8002E003300F5022E001B0015032E00230015032E002B001B032E001300F5022E003B003E032E00430015032E00530015032E00630073032E006B007C032E0073008503BC02ED020480000001000000EE0CDE9C0100000046002903000002000000000000000000000001003E000000000002000000000000000000000001007400000000000000003C4D6F64756C653E0053514C434C524E65745F4469726563746F727942726F777365722E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C650053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67006F735F6469726563746F72795F696E666F0053716C426F6F6C65616E0053716C496E7436340053716C4461746554696D65006F735F6469726563746F72795F696E666F5F46696C6C526F77002E63746F7200706174680066696C746572006F626A00616C746E616D650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650069735F6469726563746F72790073697A65006372656174656461746574696D65006C61737477726974656461746574696D65006C6173746163636573736461746574696D65006174747269620053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053514C434C524E65745F4469726563746F727942726F77736572004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F56616C75650053797374656D2E494F004469726563746F7279496E666F006765745F49734E756C6C00537472696E6700456D707479006F705F457175616C6974790046696C6553797374656D496E666F0047657446696C6553797374656D496E666F730046696C65496E666F006765745F4E616D65006F705F496D706C696369740046616C7365006765745F4C656E677468004461746554696D65006765745F4372656174696F6E54696D65006765745F4C617374577269746554696D65006765745F4C61737441636365737354696D650046696C6541747472696275746573006765745F4174747269627574657300546F537472696E670054727565004E756C6C000000032000000000009ECB0FEF72F31F419C9076BDA15576330008B77A5C561934E0890800021209110D110D190008011C10110D10111110111510111910111910111910110D03200001042001010E042001010205200101114D042001010880A00024000004800000940000000602000000240000525341310004000001000100F7671782FE28927CFC962926986C6805F2FCF63C5AE36629B96FF4E04219D3AB0CBAA70305FD298B110328AFAC7ACADC6F845C263467870B4C90E707CC84F80396E8DAA766E6FE5738F73BF896119BB97C3B8B4B7841309E1E65C2FC214CBBF4AA511652033F15820F76EF404CA389D639AA8D256EBDF82E705A122C4385ABB581B401000500540E044E616D65116F735F6469726563746F72795F696E666F540E1146696C6C526F774D6574686F644E616D65196F735F6469726563746F72795F696E666F5F46696C6C526F775455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E697374696301540E0F5461626C65446566696E6974696F6E80B76E616D65206E766172636861722834303030292C2069735F6469726563746F7279206269742C2073697A655F696E5F627974657320626967696E742C206372656174655F64617465206461746574696D652C0D0A2020202020202020202020202020202020202020202020206C6173745F7772697474656E5F746F206461746574696D652C206C6173745F6163636573736564206461746574696D652C2061747472696275746573206E766172636861722834303030290320000E0320000202060E050002020E0E0520001D12650620011D12650E070703125D120902050001110D0E030611110320000A05000111150A042000116D0600011119116D04200011710306111507070312691265021F01001A53514C434C524E65745F4469726563746F727942726F7773657200000501000000002201001D4F53492052657374617572616E7420506172746E6572732C20496E632E00003401002F436F7079726967687420C2A9204F53492052657374617572616E7420506172746E6572732C20496E632E203230303900000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000008C887A490000000002000000C4000000FC2D0000FC0F000052534453A92E044D99B24A418E5662F67CF1EFCC03000000433A5C446F63756D656E747320616E642053657474696E67735C6A6B656861796961735C4D7920446F63756D656E74735C56697375616C2053747564696F20323030385C50726F6A656374735C53514C434C524E65745F4469726563746F727942726F777365725C53514C434C524E65745F4469726563746F727942726F777365725C6F626A5C44656275675C53514C434C524E65745F4469726563746F727942726F777365722E70646200E82E00000000000000000000FE2E0000002000000000000000000000000000000000000000000000F02E00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000080400000000000000000000080434000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100DE9CEE0C00000100DE9CEE0C3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00468030000010053007400720069006E006700460069006C00650049006E0066006F0000004403000001003000300030003000300034006200300000005C001E00010043006F006D00700061006E0079004E0061006D006500000000004F00530049002000520065007300740061007500720061006E007400200050006100720074006E006500720073002C00200049006E0063002E00000060001B000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C0043004C0052004E00650074005F004400690072006500630074006F0072007900420072006F0077007300650072000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003300310030002E00340030003100350038000000000060001F00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052004E00650074005F004400690072006500630074006F0072007900420072006F0077007300650072002E0064006C006C000000000084002F0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004F00530049002000520065007300740061007500720061006E007400200050006100720074006E006500720073002C00200049006E0063002E00200032003000300039000000000068001F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052004E00650074005F004400690072006500630074006F0072007900420072006F0077007300650072002E0064006C006C000000000058001B000100500072006F0064007500630074004E0061006D00650000000000530051004C0043004C0052004E00650074005F004400690072006500630074006F0072007900420072006F0077007300650072000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003300310030002E00340030003100350038000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003300310030002E0034003000310035003800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103F00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE

GO

-- Create the Asymmetric Key from the Assembly. (More about `CREATE ASYMMETRIC KEY`: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-asymmetric-key-transact-sql?view=sql-server-ver15#:~:text=An%20asymmetric%20key%20is%20a,generates%20a%20new%20key%20pair.&text=The%20private%20key%20can%20be,1024%2C%20or%202048%20bits%20long.)
CREATE ASYMMETRIC KEY SQLCLRNet_ExampleKey 
FROM ASSEMBLY [SQLCLRNet_DirectoryBrowser]
GO

-- Create the Login from the Asymmetric Key
CREATE LOGIN SQLCLRNet_ExampleLogin 
FROM ASYMMETRIC KEY SQLCLRNet_ExampleKey
GO

-- Grant the External Access Privilege to the Login
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRNet_ExampleLogin 
GO

-- Create the database user for Authorization on the Assembly
CREATE USER SQLCLRNet_ExampleLogin FOR LOGIN SQLCLRNet_ExampleLogin
GO

-- Set Authorization to the Database User
ALTER AUTHORIZATION ON ASSEMBLY::[SQLCLRNet_DirectoryBrowser] TO SQLCLRNet_ExampleLogin
GO

-- Set the Assembly for External Access
ALTER ASSEMBLY [SQLCLRNet_DirectoryBrowser] WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

-- Create the TSQL Function that maps to the Assembly
CREATE FUNCTION [dbo].[os_directory_info](@path [nvarchar](max), @filter [nvarchar](100) = null)
RETURNS  TABLE (
    [name] [nvarchar](max) NULL,
    [is_directory] [bit] NULL,
    [size_in_bytes] [bigint] NULL,
    [create_date] [datetime] NULL,
    [last_written_to] [datetime] NULL,
    [last_accessed] [datetime] NULL,
    [attributes] [nvarchar](max) NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLCLRNet_DirectoryBrowser].[UserDefinedFunctions].[os_directory_info]

/*
You can now run this function using something like
    SELECT *
    FROM master.dbo.os_directory_info('C:\', default)
*/

Original article: Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents

Some major benefits over xp_cmdshell and xp_dirtree :

• No need for sysadmin permissions at runtime.
• No risk of feature going away do to it being undocumented.
• Limited attack surface

Another example: Yet another TVF: returning files from a directory

If you want you can achieve this using a CLR Function/Assembly.

1. Create a SQL Server CLR Assembly Project.
2. Go to properties and ensure the permission level on the Connection is setup to external
3. Add A Sql Function to the Assembly.

Here's an example which will allow you to select form your result set like a table.

public partial class UserDefinedFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.Read,
        FillRowMethodName = "GetFiles_FillRow", TableDefinition = "FilePath nvarchar(4000)")]
    public static IEnumerable GetFiles(SqlString path)
    {
        return System.IO.Directory.GetFiles(path.ToString()).Select(s => new SqlString(s));
    }

    public static void GetFiles_FillRow(object obj,out SqlString filePath)
    {
        filePath = (SqlString)obj;
    }
};

And your SQL query.

use MyDb

select * From GetFiles('C:\Temp\');

Be aware though, your database needs to have CLR Assembly functionaliy enabled using the following SQL Command.

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

CLR Assemblies (like XP_CMDShell ) are disabled by default so if the reason for not using XP Cmd Shell is because you don't have permission, then you may be stuck with this option as well... just FYI.

I hunted around for ages to find a decent easy solution to this and in the end found some ridiculously complicated CLR solutions so decided to write my own simple VB one. Simply create a new VB CLR project from the Database tab under Installed Templates, and then add a new SQL CLR VB User Defined Function. I renamed it to CLRGetFilesInDir.vb. Here's the code inside it...

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
-----------------------------------------------------------------------------
Public Class CLRFilesInDir
-----------------------------------------------------------------------------
<SqlFunction(FillRowMethodName:="FillRowFiles", IsDeterministic:=True, IsPrecise:=True, TableDefinition:="FilePath nvarchar(4000)")> _
Public Shared Function GetFiles(PathName As SqlString, Pattern As SqlString) As IEnumerable
    Dim FileNames As String()

    Try
    FileNames = Directory.GetFiles(PathName, Pattern, SearchOption.TopDirectoryOnly)
    Catch
        FileNames = Nothing
    End Try

    Return FileNames

End Function
-----------------------------------------------------------------------------
Public Shared Sub FillRowFiles(ByVal obj As Object, ByRef Val As SqlString)
    Val = CType(obj, String).ToString
End Sub

End Class

I also changed the Assembly Name in the Project Properties window to CLRExcelFiles, and the Default Namespace to CLRGetExcelFiles.

NOTE: Set the target framework to 3.5 if you are using anything less that SQL Server 2012.

Compile the project and then copy the CLRExcelFiles.dll from \bin\release to somewhere like C:\temp on the SQL Server machine, not your own.

In SSMS:-

CREATE ASSEMBLY <your assembly name in here - anything you like>
FROM 'C:\temp\CLRExcelFiles.dll';

CREATE FUNCTION dbo.fnGetFiles
(
@PathName NVARCHAR(MAX),
@Pattern NVARCHAR(MAX)
)
RETURNS TABLE (Val NVARCHAR(100))
AS
EXTERNAL NAME <your assembly name>."CLRGetExcelFiles.CLRFilesInDir".GetFiles;
GO

then call it

SELECT * FROM dbo.fnGetFiles('\\<SERVERNAME>\<$SHARE>\<folder>\' , '*.xls')

NOTE: Even though I changed the Permission Level to EXTERNAL_ACCESS on the SQLCLR tab under Project Properties, I still needed to run this every time I (re)created it.

ALTER ASSEMBLY [CLRFilesInDirAssembly] 
WITH PERMISSION_SET = EXTERNAL_ACCESS 
GO

and wullah! that should work.

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

*/

----create a work table 

CREATE TABLE [dbo].[File_List](
    [subdirectory] [varchar](250) NULL,
    [Depth] [int] NULL,
    [File] [int] NULL
) ON [PRIMARY]


declare @BasePath varchar(255) = 'I:\Test'

---  insert and  execute  system Procedure 

insert into [dbo].[File_List]
 EXEC master.sys.xp_dirtree @BasePath, 0, 1;

 -- you will have all the  files listed in the work table

Very easy, just use the SQLCMD-syntax.

Remember to enable SQLCMD-mode in the SSMS, look under Query -> SQLCMD Mode

Try execute:

!!DIR
!!:GO

or maybe:
!!DIR "c:/temp"
!!:GO