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

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)