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.
- 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.