How to list files inside a folder with SQL Server

Question

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

User · Answer

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

User · Answer

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  lt  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

User · Answer

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 -----------------------------------------------------------------------------  lt SqlFunction FillRowMethodName   FillRowFiles   IsDeterministic  True  IsPrecise  True  TableDefinition   FilePath nvarchar 4000    gt    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  lt your assembly name in here - anything you like gt  FROM  C  temp CLRExcelFiles dll    CREATE FUNCTION dbo fnGetFiles    PathName NVARCHAR MAX    Pattern NVARCHAR MAX    RETURNS TABLE  Val NVARCHAR 100   AS EXTERNAL NAME  lt your assembly name gt   CLRGetExcelFiles CLRFilesInDir  GetFiles  GO   then call it  SELECT   FROM dbo fnGetFiles     lt SERVERNAME gt   lt  SHARE gt   lt folder gt         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

User · Answer

If you want you can achieve this using a CLR Function Assembly    Create a SQL Server CLR Assembly Project  Go to properties and ensure the permission level on the Connection is setup to external 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   gt  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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server