Display all files in a directory with master.sys.xp_dirtree

23 November,2016 by Jack Vamvas

Question: I want to list all files in a folder  but not use Powershell. I’ve read SQL Server - Powershell Get-ChildItem with examples , which outlines how to list all files in a folder with the Powershell Get-ChildItem cmdlet . But due to various security policies , we can’t use this methodology.

As part of the solution , I want to place these files into a temporary table, which other sql code can use this list to complete some multiple BULK INSERT commands

Is there an extended procedure , that will support this type of result?

Answer: You can use the extended procedure master.sys.xp_dirtree.   This procedure will iterate through all files in a folder and report the list.

You can INSERT the resultset into a temporary table – which is then accessible via sql code

The master.sys.xp_dirtree has 3 input parameters.

1.directory - The directory which the procedure will read the files e.g E:\myfiles

2.depth -  How many subfolders should the xp display. For example if you need to see files within a folder use 2. The procedure will recurse through the subfolder

3.file – Use to display files as well as folders.  The default of 0 will not display any files.

This is the basic example of master.sys.xp_dirtree. Experiment with the input parameters for different recordsets

EXEC master.sys.xp_dirtree 'E:\myfolder\',0,1;

This basic example will create a temporary table , INSERT into the temporary table.

 

CREATE TABLE #myfiles (
    directory NVARCHAR(255),
    depth INT,
    file_bit BIT
)

INSERT INTO #myfiles
EXEC master.sys.xp_dirtree 'E:\Chargeback\',0,1;

select * from #myfiles

drop table #myfiles

Read More

SQL Server - Search and Delete files from a text based list with Powershell

SQL Server - BULK INSERT with ERRORFILE (SQL Server DBA)


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Display all files in a directory with master.sys.xp_dirtree


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer