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.

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)

About these ads

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

  1. dear sir,
    i have tried this , it retreives 15112 rows from system. but my question is why this one needed, please explain me sir..

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

  3. 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’.

    Thanks

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