[sql] How to list files inside a folder with SQL Server

How do I list files inside a folder in SQL Server without using the xp_cmdshell stored procedure?

Create a SQLCLR assembly with external access permission that returns the list of files as a result set. There are many examples how to do this, eg. Yet another TVF: returning files from a directory or Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents.

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.

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

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

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.

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