Dec 312012
 

In a previous blog post, I demonstrated how you can use an undocumented stored procedure, master.sys.xp_dirtree, to list all files in a folder.  The previous posting mostly talked about how the stored procedure worked and the output it generated.  For this posting, I have expanded upon the script to parse all subdirectories and list all files in all subfolders from the starting point.

Just for a quick review, xp_dirtree has three parameters:
  1. directory – This is the directory you pass when you call the stored procedure; for example ‘D:Backup’.
  2. depth  – This tells the stored procedure how many subfolder levels to display.  The default of 0 will display all subfolders.
  3. isfile – This will either display files as well as each folder.  The default of 0 will not display any files.

Now for the updated script:

DECLARE
       @BasePath varchar(1000)
      ,@Path varchar(1000)
      ,@FullPath varchar(2000)
      ,@Id int;
–This is your starting point.
SET @BasePath = ‘D:Backup’;
–Create a temp table to hold the results.
IF OBJECT_ID(‘tempdb..#DirectoryTree’)IS NOT NULL
      DROP TABLE#DirectoryTree;
CREATE TABLE #DirectoryTree (
       id int IDENTITY(1,1)
      ,fullpath varchar(2000)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);
–Create a clustered index to keep everything in order.
ALTER TABLE #DirectoryTree
ADD CONSTRAINT PK_DirectoryTree PRIMARYKEY CLUSTERED (id);
–Populate the table using the initial base path.
INSERT#DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @BasePath,1,1;
UPDATE#DirectoryTree SET fullpath = @BasePath;
–Loop through the table as long as there are still folders to process.
WHILE EXISTS (SELECT id FROM #DirectoryTree WHEREisfile = 0)
BEGIN
      –Select the first row that is a folder.
      SELECT TOP (1)
             @Id = id
            ,@FullPath = fullpath
            ,@Path = @BasePath + + subdirectory
      FROM #DirectoryTree WHERE isfile = 0;
      IF @FullPath = @Path
      BEGIN
            –Do this section if the we are still in the same folder.
            INSERT #DirectoryTree (subdirectory,depth,isfile)
            EXEC master.sys.xp_dirtree @Path,1,1;
            UPDATE #DirectoryTree
            SET fullpath = @Path
            WHERE fullpath IS NULL;
            –Delete the processed folder.
            DELETE FROM #DirectoryTree WHEREid = @Id;
      END
      ELSE
      BEGIN
            –Do this section if we need to jump down into another subfolder.
            SET @BasePath = @FullPath;
            –Select the first row that is a folder.
            SELECT TOP (1)
                   @Id = id
                  ,@FullPath = fullpath
                  ,@Path = @BasePath + + subdirectory
            FROM #DirectoryTree WHERE isfile = 0;
            INSERT #DirectoryTree (subdirectory,depth,isfile)
            EXEC master.sys.xp_dirtree @Path,1,1;
            UPDATE #DirectoryTree
            SET fullpath = @Path
            WHERE fullpath IS NULL;
            –Delete the processed folder.
            DELETE FROM #DirectoryTree WHEREid = @Id;
      END
END
–Output the results.
SELECT fullpath + + subdirectory AS ‘CompleteFileList’
FROM#DirectoryTree
ORDER BY fullpath,subdirectory;
–Cleanup.
IF OBJECT_ID(‘tempdb..#DirectoryTree’)IS NOT NULL
      DROP TABLE#DirectoryTree;
GO

This code will process one folder level at a time since we’re specifying 1 for the depth parameter.  For each folder in the #DirectoryTree table, we get a list of files and subfolders and insert it to the #DirectoryTree table. 
INSERT#DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @Path,1,1;

If it’s a folder, then it the file parameter will be 0 and the WHILE loop will process once it gets further down the loop.  The folder needs a clustered index to keep all the rows in order.

ALTER TABLE #DirectoryTree
ADD CONSTRAINT PK_DirectoryTree PRIMARYKEY CLUSTERED (id);

After each folder is processed, the folder row is deleted from the table.  This is necessary so the WHILE loop doesn’t turn into an infinite loop.
DELETE FROM #DirectoryTree WHEREid = @Id;

The full path is being saved for output later, so comparing it to the current base path will let us know if we need to update the @BasePathparameter.
IF @FullPath = @Path
      –Continue processing the same folder.
ELSE
      SET @BasePath = @FullPath;
      –Start processing the next subfolder.

This is how we can keep looping through each subfolder until each one has been processed.

The final step is to output the results.

SELECT fullpath + + subdirectory AS ‘CompleteFileList’
FROM#DirectoryTree
ORDER BY fullpath,subdirectory;
I have gotten pretty good performance out of this script.  Even traversing the C:Windows folder on my test VM, I can get the report to return all 62000+ files in 14000+ folders within about 6 minutes. Hopefully this example will prove to be helpful for your everyday tasks.  Enjoy!

  4 Responses to “How to Use xp_dirtree to List All Files in a Folder – Part 2”

  1. Hello Patrick,
    Is there a chance to get the creation-date of the files as well?

    Best regards
    Martin

    • No. Xp_dirtree is very limited on the data it returns. You’ll have to use something like PowerShell to get more detailed information.

      • Hello Patrick and Martin. Yes, xp_dirtree is limited, but starting in SQL Server 2017, you can use: sys.dm_os_enumerate_filesystem(). This is a function so you don’t need to dump the results into a temp table or table variable. You can also pass in a regular DOS wildcard (* = match zero or more characters ; ? = match exactly 1 character) as the second parameter:


        SELECT *
        FROM sys.dm_os_enumerate_filesystem(N'C:\temp\', N'*.txt');

        -- or:

        SELECT *
        FROM sys.dm_os_enumerate_filesystem(N'C:\temp\', N'Test???x.txt');

        Prior to SQL Server 2017 (and even since then) you can use SQLCLR to create effectively the same function. In fact, I did this many years ago in my SQL# library. I should mention that the File_GetDirectoryListing TVF is not in the Free version, but it is fast, it can get ACL/ownership (i.e. Security Descriptor, Owner SID, and Group SID) info (this does slow it down a little, hence it’s optional), uses RegEx for directory and filename filters, supports long paths, etc.

        Take care, Solomon

        • Solomon,
          It’s been a while. Hope all is well with you.

          Yes, you are correct. I forgot that SQL 2017 added a few new DMVs for viewing info about the file system. I’m so accustomed to using PowerShell nowadays for anything outside of SQL.

          Thanks,
          Patrick

Leave a Reply to Patrick Keisler Cancel reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

This site uses Akismet to reduce spam. Learn how your comment data is processed.