[转]How to Use xp_dirtree to List All Files in a Folder

本文转自:http://www.sqlservercentral.com/blogs/everyday-sql/2012/11/13/how-to-use-xp_dirtree-to-list-all-files-in-a-folder/

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: 
 
  1. directory - This is the directory you pass when you call the stored procedure; for example 'D:\Backup'.
  2. depth  - This tells the stored procedure how many subfolder levels to display.  The default of 0 will display all subfolders.
  3. 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.
 
 
 

 

posted on 2017-12-18 11:44 freeliver54 阅读(...) 评论(...) 编辑 收藏

导航

公告