What is XP_DIRTREE?  What are the alternatives to XP_Dirtree

Aman Raiyyani

This extended and undocumented stored procedure can be used to list all folders and subfolders (recursively) from a given directory. 

XP_DIRTREE has three parameters:  

  1. Directory – This is the directory you pass when you call the stored procedure; for example, ‘D:\Backup_Files’. 
  1. Depth – This tells the stored procedure how many subfolder levels to display.  The default of 0 will display all subfolders. 
  1. Files – This will either display files as well as each folder.  The default of 0 will not display any files. 

XP_DIRTREE is an undocumented and unsupported command. At times when the servers reboot the underlying DLL file doesn’t register and could cause xp_dirtree to not work. 

Here are some ways how you can troubleshoot this and make it work: 


  1. Reboot the server where the XP_DIRTREE issue is happening (This will need to be coordinated with teams using the server) 
  1. If #1 doesn’t work, then one could use non-SQL code or CLI options like CMD/PowerShell to browse through the files as XP_DIRTREE is a functionality of SQL Server that is not supported or documented. 
    Call a PowerShell script from SQL 
    ls <SourceFolder*> | Select-Object NAME | Sort-Object LastWriteTime | foreach{if(-not [System.IO.File]::Exists(''<ArchiveFolder*>\''+ $_.Name)){$_.Name} } | out-File -FilePath <ArchiveFolder*>\ProcessingFiles.txt 
  1. If multiple servers are involved in the process, make sure the SPNs are configured correctly. 


Please note: Raising an MSFT case may not help given that XP_DIRTREE is not supported or undocumented. 

When Microsoft had an agreement to share the Sybase SQL Server source code years back, and Microsoft remarketed it as “SQL Server” on the PC platform, XP_DIRTREE was one of the primitive commands SQL Server inherited. 

Alternatives of XP_DIRTREE: 

  1. XP_CmdShell is available on all versions of SQL Server but it is a risky operation to use, in terms of security and server resources. 
  1. You can also use PowerShell or SMO. 
Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…