SQL Stored Procedures to get folder and files

 

SQL Stored Procedures to get folder and files

There may be a time when you will need to get the file name contents from a folder for the purpose of using the information in your stored procedure.  The one undocumented stored procedure on Microsoft SQL Server 2016 is master.sys.xp_dirtree that you can use.

In your stored procedure, we will go ahead and create a temporary table that will store the files with their correct extension.  The first thing we want to do is check whether the temporary table was left over and remove it.  Usually when you close out a session temporary tables will be destroyed but it is fine to check and do house cleaning in  your stored procedure.

IF OBJECT_ID(‘tempdb..#FilesListing’) IS NOT NULL
DROP TABLE #FilesListing;

The next step is to create the temporary table again call it #FilesListing or whatever you wish to call it.  This table will have an identity field which will be seeded with 1 and incremented by 1.  The file name will be stored in FullFileName, you will see in a short period of time depth and isfile are used to store the information provided by the undocumented extended stored procedure; master.sys.xp_dirtree.

CREATE TABLE #FilesListing (
id int IDENTITY(1,1),
FullFileName nvarchar(512),
depth int,
isfile bit);

Preparing master.sys.xp_dirtree

master.sys.xp_dirtree has three parameters:

1. directory – This is the directory you pass when you call the stored procedure; like our example of C:\TEMP.
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.

INSERT #FilesListing (FullFileName, depth, isfile )
EXEC master.sys.xp_dirtree ‘C:\TEMP’, 1, 1;

You can play with changing the depth and file settings from 0 to 1 to see the different results you can gather.  Finally by placing a select statement from the temporary table #FilesListing will provide the results.

Select * from #FilesListing;

Wrap  up

Depth is 1 and file is 1 will show only the directory names in the results

Depth is 0 and file is 1 will show the directory and the file names in the results.

As you can see you can do a lot, one idea are nightly files deposited in a folder for the SQL Agent to kick off a stored procedure which needs to process them.  The performance is quick but you need to be extremely careful where you are pulling files from since listing the entries into the table may take a few minutes.