Archive

Posts Tagged ‘xp_dirtree’

Stored procedures to retrieve directory listing – Undocumented XPs

May 12, 2011 9 comments

You can use xp_subdirs and xp_dirtree undocumented stored procedures to retrieve a list of child directories under a specified parent directory from file system.

— © 2011 – Vishal (http://SqlAndMe.com)

EXEC master..xp_subdirs 'C:\\Inetpub'

xp_subdirs – lists only directories which are direct children of the specified parent. In this case it returns directories which are directly under 'C:\Inetpub' as below:

subdirectory

—————————

AdminScripts

custerr

history

logs

temp

wwwroot

 

(6 row(s) affected)

If you need to retrieve the children recursively, you will need to use xp_dirtree.

— © 2011 – Vishal (http://SqlAndMe.com)

 

EXEC master..xp_dirtree 'C:\\Program Files\\Microsoft.NET'

This returns all the children of 'C:\Program Files\Microsoft.NET' recursively, and their level from parent:

subdirectory                depth

——————–          ———–

ADOMD.NET                         1

100                               2

Resources                         3

1033                              4

110                               2

Resources                         3

1033                              4

Primary Interop Assemblies        1

RedistList                        1

 

(9 row(s) affected)

You can also restrict the number of level retrieved using:

— © 2011 – Vishal (http://SqlAndMe.com)

 

EXEC master..xp_dirtree 'C:\\Program Files\\Microsoft.NET', 2

This will only retrieve children of 'C:\Program Files\Microsoft.NET' whose level <= 2

subdirectory               depth
——————–             ———–
ADOMD.NET                         1
100                               2
110                               2
Primary Interop Assemblies        1
RedistList                        1

(5 row(s) affected)

Note: These procedures are undocumented and unsupported by Microsoft and they may *disappear* from newer SQL Server versions!

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe