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 (https://blog.sqlauthority.com/)
7 Comments. Leave new
dear sir,
i have tried this , it retreives 15112 rows from system. but my question is why this one needed, please explain me sir..
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
Is there a way to get the metadata of the file, like, date created,date modified?
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.
You need to map the drive within SQL Server using xp_cmdshell and net use command. I will try to write a blog post on this.
Is there a way to get the parent directory? For example, use the path “C:\Users\MyUserName\mytextfile.txt” and get “C:\Users\MyUserName”.
Thank you.
Why don’t see this xp_dirtree in Azure sql server