How to list files inside a folder with SQL Server

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://docs.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://docs.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.