Nov 132012

UPDATED — Dec 31, 2012 — Be sure to read Part 2 of this post discussing xp_dirtree.

Last week I blogged about how to use an undocumented stored procedures to create folders.  This week we need to do the opposite.  We need to get a list of all files from a subfolder in order to process them.  In order to do that, we’ll use another undocumented extended stored procedure; master.sys.xp_dirtree.  This stored procedure will display a list of every folder, every subfolder, and every file for path you give it.

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. file – This will either display files as well as each folder.  The default of 0 will not display any files.

For today’s example, we just want to display all of our backup files (*.BAK) in a particular folder. We need to all of the other parameters in order to show the files as well as any subfolders.

EXEC master.sys.xp_dirtree ‘D:BackupTRON4TEST2MyDb1’,0,1;

The output below will show us each subfolder and every file for the given directory.
We’re only concerned about the BAK files.  So we’ll need to create a temp table to hold the output of xp_dirtree.  Then we can select only the rows that are an actual file (isfile = 1) and that have a file extension of BAK.

IF OBJECT_ID(‘tempdb..#DirectoryTree’)IS NOT NULL
      DROP TABLE#DirectoryTree;
CREATE TABLE #DirectoryTree (
       id int IDENTITY(1,1)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);
INSERT#DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree ‘D:BackupTRON4TEST2MyDb1’,1,1;
SELECT * FROM #DirectoryTree
WHERE isfile =AND RIGHT(subdirectory,4) = ‘.BAK’

Below is the output.
This example only shows the files for a specific subfolder, which is used to get a list of files that are needed for purging before another database backup runs.  This stored procedure also can accept UNC paths (\ServerFolder) to get a list of files and subfolders from a network drive.  In a future post, I will add on to this script to show all files in all subfolders for a given directory.  Until then, play around with xp_dirtree and its parameters to see the different outputs.  You may be able to incorporate this into your own code.

  7 Responses to “How to Use xp_dirtree to List All Files in a Folder”

  1. Very helpful, thanks. You can also use ip address to access files on another machine:

    EXEC master.sys.xp_dirtree ‘\\9.9.9\C$\\users\Bullwinkle\’,1,1;

  2. Thanks Patrick.

    Is there any way to COUNT the number of files in a directory without listing them all first with ‘xp_dirtree’?

    I appreciate your help.

    • I am not aware of any way to access the file system through SQL server other than xp_dirtree. The example in this article loads the files into a temp table where you could perform a count against that table.

  3. Hi my EXEC master.sys.xp_DirTree ‘C:\ KWS’,0,1 returns no results. but the directory has files. Is there something i might be missing?

    DECLARE @Path nvarchar(500)
    SET @Path = ‘C:\ KWS’ –Change the path’

    (FileNames nvarchar(500)
    ,depth int ,isFile int)

    INSERT INTO @FindFile
    EXEC master.sys.xp_DirTree @Path,0,1
    SELECT FileNames
    FROM @FindFile WHERE isFile=1

  4. Perfect we are getting incremental files from our source erp system (ERP LN) there is no option to get 1 file after a day. Even if there is a commit a file is generated.
    This is causing that some tables have 50K files per day. Loading this file by file sequential is not performing. I wasn’t expecting to get this amount of files.
    But with the usp above i can query the folder and start multiple parallel loads instead off only 1.

 Leave a 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>



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