RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL
Expertise: Intermediate
Jun 1, 2017

Using xp_ dirtree to List Folders, Subfolders and Files with SQL

You can use an undocumented Extended Stored Procedure named xp_dirtree to list folders, subfolders, and files for a given path. Xp_dirtree has three parameters that need to be supplied:

  • directory - The given directory.
  • depth - How many subfolder levels to display. 0 will display all subfolders.
  • file - This will either display files as well as each folder. 0 will not display any files.

Here's an example of its usage:

DECLARE @DBName nvarchar(100)
DECLARE @Folder nvarchar(100)
SET @DBName = 'TestDB'
SET @Folder = 'C:\TestFolder\' + @DBName
EXEC master.sys.xp_dirtree @Folder, 0, 1; 
Hannes du Preez
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date