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