Patrick Keisler is a MCTS and MCP. For over 12 years, he has been been a database administrator for a major investment bank, Wells Fargo Securities. During that time, he has gained considerable knowledge in Microsoft SQL Server by supporting hundreds of applications ranging from high-volume trading applications to massive data warehouses.
-- 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.