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' + @DBNameEXEC master.sys.xp_dirtree @Folder, 0, 1;