Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, Undocumented Functions > Stored procedures to retrieve directory listing – Undocumented XPs

Stored procedures to retrieve directory listing – Undocumented XPs


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 FilesMicrosoft.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 FilesMicrosoft.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

  1. Vishal
    16.06.2011 01:46 | #1

    check if any attributes are set on the .jpg files (hidden/system). Also, check if SQLServer service account has valid permissions..

  2. Amit
    16.06.2011 12:45 | #2

    Hi Vishal,

    I have some work around with XP_Dirtree…… but couldn’t succeeded any how….

    My Scenario is :

    I have folder Test in E Drive , and have few jpg file inside Test Folder, but when i used below script it will give no result..

    Script EXEC xp_dirtree ‘E:, 2, 1

    As per my understanding , Test folder is just like that E:Test, so depth should be 2 and i am looking for files , so third parameter is also 1.

    Again i have tried the same with C drive as below

    EXEC xp_dirtree ‘C:’, 2, 1

    Thanks to heaven , i am able to see some result this time, but some bad news came my way.

    This time i have folder WS, what is more peculiar is i am able to see some txt file which shares location with jpg file under the folder WS, but couldn’t able to see jpg files.

    Please me know where i have been mistaken.

    It should be appreciable if you come with some sort of resolution for the same.

    Amit

  3. Amit
    16.06.2011 12:52 | #3

    Amit :
    Hi Vishal,
    I have some work around with XP_Dirtree…… but couldn’t succeeded any how….
    My Scenario is :
    I have folder Test in E Drive , and have few jpg file inside Test Folder, but when i used below script it will give no result..
    Script EXEC xp_dirtree ‘E:, 2, 1
    As per my understanding , Test folder is just like that E:Test, so depth should be 2 and i am looking for files , so third parameter is also 1.
    Again i have tried the same with C drive as below
    EXEC xp_dirtree ‘C:’, 2, 1
    Thanks to heaven , i am able to see some result this time, but some bad news came my way.
    This time i have folder WS, what is more peculiar is i am able to see some txt file which shares location with jpg file under the folder WS, but couldn’t able to see jpg files. i.e when i executed EXEC xp_dirtree ‘C:’, 2, 1 i am able to see txt files as a output but not able to see jpg files though jpg files are also present under the folder WS along with txt files
    Please me know where i have been mistaken.
    It should be appreciable if you come with some sort of resolution for the same.
    Amit

  4. Amit
    17.06.2011 02:11 | #4

    HI Vishal,
    1. Atrritbuts for jpg file is neither READ ONLY nor Hidden.
    2. what kind of permission should i check for SQL SERVER SERVICE.
    3. Why EXEC xp_dirtree ‘E:, 2, 1 is not working

    Amit

  5. Amit
    17.06.2011 02:20 | #5

    .

  6. Vishal
    17.06.2011 04:37 | #6

    1. check from command prompt using “Attrib” command..
    2. which account are you using as SQLServer service account? check permissions for that..
    3. this issue is also related to permissions, the SQLServer service account may not have acces to E: (Is this a mapped drive?)

  7. John Bevilaqua
    15.12.2011 02:43 | #7
  8. Z
    18.02.2013 10:35 | #8

    Hi Vishal,
    As you are an experienced person, I wanted to ask you the question below:
    How can I get the list of documented system SPs in SQL server? BOL does not give enough information.

  9. Vishal
    19.02.2013 04:18 | #9

    Hi,

    You can get this list from sys.all_objects.

    SELECT name FROM sys.all_objects WHERE type = ‘P’;

  1. No trackbacks yet.