SQL SERVER – Get Directory Structure using Extended Stored Procedure xp_dirtree

Many years ago I wrote article SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server where I demonstrated using undocumented Stored Procedure to find the drive letter in local system and available free space. I received question in email from reader asking if there any way he can list directory structure within the T-SQL. When I inquired more he suggested that he needs this because he wanted set up backup of the data in certain structure.

Well, there is one undocumented stored procedure exists which can do the same. However, please be vary to use any undocumented procedures.

xp_dirtree 'C:\Windows'

Execution of the above stored procedure will give following result. If you prefer you can insert the data in the temptable and use the same for further use.

xpdirtree SQL SERVER   Get Directory Structure using Extended Stored Procedure xp dirtree

Here is the quick script which will insert the data into the temptable and retrieve from the same.

CREATE TABLE #TempTable (Subdirectory VARCHAR(512), Depth INT);
INSERT INTO #TempTable (Subdirectory, Depth)
EXEC xp_dirtree 'C:\Windows'
SELECT Subdirectory, Depth
FROM #TempTable;
DROP TABLE #TempTable;

Reference: Pinal Dave (http://blog.SQLAuthority.com)

6 thoughts on “SQL SERVER – Get Directory Structure using Extended Stored Procedure xp_dirtree

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #005 « SQL Server Journey with SQL Authority

  2. Hello Sir,
    This script is awesome to list available directory and files on specified path from network computer and also we can find out the depth of sub directory or file.
    But, the question is that how do i know parent directory of listed file or folder if depth is more than 1 and how do i find out the complete file path of listed item. Otherwise to make a tree view for file folder on network drive this method is great.
    One more question can we search specific file using wildcard character without creating a temporary table just like xp_cmdshell ‘ /b dir D:\*.jpg’.



  3. Hello Pinal.
    I am having difficulty reading a network share that is in the same domain, and permissions are ok. I checked the logs on Windows Server 2008, and it indicates that successful directory read occurred. However, no data shows up. I can successfully enumerate folders and files on the local server, just not remote servers. Any suggestions? Thank you.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s