xp_dirtree (T-SQL)

It is possible to get the content of a directory from T-SQL with the Extended Stored Procedure (XP) master.dbo.xp_dirtree. This only requires the SQL Server service account to have access to the directory and have browse rights.

Syntax:

EXECUTE master.dbo.xp_dirtree <Directory>, <Depth>, <IsFile>

• <Directory> nvarchar(???): Full path (name) of directory. Can be UNC path. The value can end with '\' or not.
• <Depth> int: Depth of list in directory.
• <IsFile> int: Show if row is on a file. If the value in non-zero then the result will show if a row is in a file.

The result is a table with the three columns subdirectory, depth and file where

• subdirectory is the name of the subdirectory or file in the given directory.
• depth is the depth of the directory/file in the given directory. One ('1') is the root of the current directory.
• file indicates if the directory is a file ('1') or not ('0'). If not a file then it is a directory.

For some reason the value for <Directory> can't be from SERVERPROPERTY like
EXECUTE master.dbo.xp_dirtree SERVERPROPERTY('InstanceDefaultBackupPath'), 1,1;.
The error is
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'InstanceDefaultBackupPath'.
Not even with CONVERT like
EXECUTE master.dbo.xp_dirtree CONVERT(nvarchar(512), SERVERPROPERTY('InstanceDefaultBackupPath')), 1,1;
where the error is
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.

The system function SERVERPROPERTY can be used through a temporary variable like this
DECLARE @_backuppath nvarchar(512) = CONVERT(nvarchar(512), SERVERPROPERTY('InstanceDefaultBackupPath'));
EXECUTE master.dbo.xp_dirtree @_backuppath, 1,1;

Example:

EXECUTE master.dbo.xp_dirtree N'D:\MSSQL\Backup', 1, 1;
List (backup) files in folder.

Unfortunately this Extended Stored Procedure is not documentd by Microsoft. Like many other XP's...

History

2024-06-25: Examples on SERVERPROPERTY added.
2022-03-25: Post published.