Use xp_dirtree SQL Server Stored Procedure and Recursive CTE Query to List Hierarchical Structure of File Folders

T-SQL developers can use xp_dirtree SQL Server stored procedure to display subdirectory list beneath a given file folder as input parameter.
xp_dirtree extended stored procedure is one of the SQL Server undocumented stored procedures.
In this SQL tutorial, we will use SQL Server xp_dirtree stored procedure with T-SQL recursive CTE query in order to list hierarchical structure of file folders.

Let's start this sql tutorial with the basic syntax of xp_dirtree SQL Server stored procedure.
Here is the sql xp_dirtree syntax for t-sql developers.


DECLARE @folderpath nvarchar(4000) = 'C:\Program Files\Microsoft SQL Server\'

EXEC master..xp_dirtree @folderpath

-- OR
EXEC master..xp_dirtree 'C:\Program Files\Microsoft SQL Server\'

Code

When you execute xp_dirtree SQL procedure the returned sql output includes two data columns "subdirectory" and "depth".

sql-server-xp_dirtree-tsql-undocumented-stored-procedure

With t-sql developers and SQL Server administrators, we will continue this sql tutorial to display a hierarchical structure list of file folders using SQL Server xp_dirtree stored procedure and SQL Recursive CTE expressions.

Let's first create sql table DirTree to store the directory structure which will return from sql xp_dirtree stored procedure. T-SQL developers will later update stored data to mark their parent directories in the sql DirTree table. SQL developers also create the SQL Server Recursive CTE query on the DirTree table data.


CREATE TABLE DirTree (

 Id int identity(1,1),

 SubDirectory nvarchar(255),

 Depth smallint,

 ParentDirectory int

)

Code

After the DirTree table is created in the SQL database, let's populate sql table with sample data.
I wanted to work with file folders under the Microsoft SQL Server folder in Program Files.


INSERT INTO DirTree (SubDirectory, Depth)

EXEC master..xp_dirtree 'C:\Program Files\Microsoft SQL Server\'

Code

I had 428 items in the DirTree sql table. Of course this rows count will differ according to your installed SQL Server instances and installed features.
If you run a SELECT query on sql DirTree table, you will see the SubDirectory text field and Depth integer data.

SQL developers can update the DirTree table using the below sql script in order to mark Each row's parent directory Id.


UPDATE DirTree

SET ParentDirectory = (

 SELECT MAX(Id) FROM DirTree

 WHERE Depth = d.Depth - 1 AND Id < d.Id

)

FROM DirTree d

Code

Now, if you execute sql SELECT statement on DirTree table, you will see that we have now ParentDirectory field updated with calculated parent directory id value.

sql-xp_dirtree-stored-procedure-for-recursive-sql-directory-listing

The last step is to create recursive SQL CTE query to select directory list with parent directory as seen in the below T-SQL CTE script


WITH CTE as (

 SELECT

  Id, CAST(SubDirectory as nvarchar(255)) as SubDirectory,

  Depth, ParentDirectory, CAST('' as nvarchar(255)) as Parent

 FROM DirTree WHERE ParentDirectory IS NULL

 UNION ALL

 SELECT

  d.Id, CAST(d.SubDirectory as nvarchar(255)),

  d.Depth, d.ParentDirectory, CAST(CTE.SubDirectory as nvarchar(255))

 FROM DirTree d

 INNER JOIN CTE ON d.ParentDirectory = CTE.Id

)

SELECT * FROM CTE ORDER BY Id

Code

Here is the resultant hierarchical file folder structure under "Microsoft SQL Server" directory in Program Files using SQL xp_dirtree undocumented stored procedure and SQL Recursive CTE query.

microsoft-sql-server-directory-structure-using-xp_dirtree-recursive-cte-query