Get a List of Fixed Hard Drive and Free Space on Server

If I do not have access to OS I use the following Stored Procedure to figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives. 

EXEC master..xp_fixeddrives

Get Directory Structure using Extended Stored Procedure xp_dirtree

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;