Patrick Keisler is a Premier Field Engineer for Microsoft with over 15 years of SQL Server experience working in various fields such as financial, healthcare, and government. He currently holds two MCITP certifications in SQL Server 2008. You can follow him on Twitter or listen to him speak at various SQL Saturdays and user group meetings.
-- Dec 31, 2012 -- Be sure to read Part 2
of this post discussing xp_dirtree
Last week I blogged about how to use an undocumented stored procedures to create folders. This week we need to do the opposite. We need to get a list of all files from a subfolder in order to process them. In order to do that, we'll use another undocumented extended stored procedure; master.sys.xp_dirtree
. This stored procedure will display a list of every folder, every subfolder, and every file for path you give it.
Xp_dirtree has three parameters:
- directory - This is the directory you pass when you call the stored procedure; for example 'D:\Backup'.
- depth - This tells the stored procedure how many subfolder levels to display. The default of 0 will display all subfolders.
- file - This will either display files as well as each folder. The default of 0 will not display any files.
For today's example, we just want to display all of our backup files (*.BAK) in a particular folder. We need to all of the other parameters in order to show the files as well as any subfolders.
EXEC master.sys.xp_dirtree 'D:\Backup\TRON4\TEST2\MyDb1',0,1;
The output below will show us each subfolder and every file for the given directory.
We're only concerned about the BAK files. So we'll need to create a temp table to hold the output of xp_dirtree
. Then we can select only the rows that are an actual file (isfile = 1
) and that have a file extension of BAK.
IF OBJECT_ID('tempdb..#DirectoryTree')IS NOT NULL DROP TABLE#DirectoryTree; CREATE TABLE #DirectoryTree ( ,subdirectory nvarchar(512) INSERT#DirectoryTree (subdirectory,depth,isfile) EXEC master.sys.xp_dirtree 'D:\Backup\TRON4\TEST2\MyDb1',1,1; SELECT * FROM #DirectoryTree
WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.BAK'
Below is the output.
This example only shows the files for a specific subfolder, which is used to get a list of files that are needed for purging before another database backup runs. This stored procedure also can accept UNC paths (\\Server\Folder) to get a list of files and subfolders from a network drive. In a future post, I will add on to this script to show all files in all subfolders for a given directory. Until then, play around with xp_dirtree and its parameters to see the different outputs. You may be able to incorporate this into your own code.