Understanding Directory and File System Queries in SQL Server

SQL Server provides several undocumented stored procedures (SPs) for querying the directory and file system. These SPs can be useful for retrieving information about files and directories, such as counting the total number of files in a folder or loading the structure of a table into multiple tables.

Introduction to XP Dirtree

One of these SPs is xp_dirtree, which was introduced in SQL Server 2008. It provides a way to query the directory and file system, allowing developers to retrieve information about files and directories without having to write custom code.

What does xp_dirtree do?

The xp_dirtree SP takes three parameters: the path to the directory or folder, the depth level (0 = top-level directory, 1 = subdirectory), and an optional parameter to specify whether to return only files or also directories. The SP returns a result set containing the following columns:

• [subdirectory]: the name of the directory or file
• [depth]: the depth level of the directory or file (0 for top-level, 1 for subdirectory)
• [file]: a binary indicator indicating whether the returned row is a file (1) or directory (0)

Using xp_dirtree

To use xp_dirtree, you can execute it in SQL Server Management Studio (SSMS) using the following syntax:

exec xp_dirtree 'C:\Program Files\', 1, 1;

This would return the structure of the C:\Program Files\ directory, with only one level down.

Counting Total Number of Files in a Folder

To count the total number of files in a folder, you can use the following SQL query:

select count(1) from #directory where [file] = 1;

However, this will not return the correct result, as it only counts the top-level directories. To get the correct count, you need to modify the #directory table to include all subdirectories.

Modifying #directory Table

To do this, we can modify the #directory table to include an additional column for the subdirectory depth level. We can then use a recursive Common Table Expression (CTE) to traverse the directory structure and load all files into the table.

Here’s how you can create and populate the #directory table:

if object_id('tempdb..#directory','U') is not null
    begin
        drop table #directory;
    end;

create table #directory (
     [subdirectory] varchar(512) 
    ,[depth] int 
    ,[file] int
);

insert into #directory ([subdirectory],[depth],[file])
exec xp_dirtree 'C:\Program Files\',0,1;

Recursive CTE to Traverse Directory Structure

To traverse the directory structure recursively and load all files into the table, we can use a recursive CTE. Here’s an example of how you can create and populate the #directory table using a recursive CTE:

if object_id('tempdb..#directory','U') is not null
    begin
        drop table #directory;
    end;

create table #directory (
     [subdirectory] varchar(512) 
    ,[depth] int 
    ,[file] int
);

declare @sql nvarchar(max);
set @sql = 'with directory_cte as (select ''' + substring(xp_dirtree(''C:\Program Files\''', 0, 1), charindex('''
,' , xp_dirtree(''C:\Program Files\''', 0, 1)) + 1) from master..sysobjects where object_id = ''xp_dirtree'' and type = '''sp''') as a
from directory_cte
couter join (
    select [subdirectory] = left([subdirectory], charindex('''
,' , [subdirectory]) - 1)
   , [depth] = case when charindex('''
, [subdirectory] = '' then 0 else charindex('''
+ 1 end + 1
     , [file] = 0
) from directory_cte a
    exec xp_dirtree ('C:\Program Files\', @a.[subdirectory], 1,1);';

exec sp_executesql @sql;

This will create the #directory table and populate it with all files in the specified directory.

Loading Data into Multiple Tables

To load the data from the #directory table into multiple tables, you can use a loop to execute the same query for each table. Here’s an example of how you can do this:

declare @table_name nvarchar(256) = 'table1';
while @@ERROR == 0 and exists (select * from sys.tables where name = @table_name)
begin
    set @table_name += ', table2, table3';

    declare @sql nvarchar(max);
    set @sql = 'insert into ' + @table_name + ' ([subdirectory],[depth],[file]) 
       select [subdirectory], [depth], [file] from #directory 
       where [file] = 1';

    exec sp_executesql @sql;
end

This will create three tables (table1, table2, and table3) and load the data from the #directory table into each of them.

Conclusion

In this article, we discussed how to use SQL Server’s undocumented stored procedure xp_dirtree to query the directory and file system. We also showed how to modify the #directory table to include an additional column for the subdirectory depth level and how to traverse the directory structure recursively using a recursive CTE. Finally, we demonstrated how to load the data from the #directory table into multiple tables using a loop.

Last modified on 2023-10-20