SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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\'

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


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.

 Id int identity(1,1),
 SubDirectory nvarchar(255),
 Depth smallint,
 ParentDirectory int

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\'

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.

SET ParentDirectory = (
 WHERE Depth = d.Depth - 1 AND Id < d.Id
FROM DirTree d

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.


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

  Id, CAST(SubDirectory as nvarchar(255)) as SubDirectory,
  Depth, ParentDirectory, CAST('' as nvarchar(255)) as Parent
 FROM DirTree WHERE ParentDirectory IS NULL
  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

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.


SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.