By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

November 7, 2005

The incredible power of SQL Server 2005 is demonstrated below by a stored procedure which maps a file system directory. It applies a recursive CTE to help in the mapping. The levels go to 12, but that can easily be extended if needed.

This is the code sample:

USE AdventureWorks;
GO

drop proc DirTree
go
CREATE PROC DirTree @Folder varchar(100)
AS
BEGIN
-- Source: www.sqlusa.com
-- Author: Kalman Toth
-- November 7, 2005

-- Get row folder map into a temporary table
CREATE TABLE #DirTree (ID int IDENTITY(1,1),
SubDir varchar(100), Depth int, ParentID int default(1))
INSERT #DirTree (SubDir, Depth, ParentID)
SELECT @Folder,0,0
INSERT #DirTree(SubDir,Depth)
EXEC master.sys.xp_dirtree @Folder

-- Update ParentID
UPDATE #DirTree set ParentID=b.ID
FROM #DirTree, #DirTree b
WHERE #DirTree.ID > b.ID
and b.ID = (SELECT max(ID) FROM #DirTree c
WHERE c.ID < #DirTree.ID and #DirTree.Depth = c.Depth+1);

-- Create a table for results
IF NOT EXISTS (SELECT 1 FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[DirectoryTree]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[DirectoryTree](
[DirectoryTreeID] [int] IDENTITY(1,1) NOT NULL,
[Root] [varchar](100) NULL,
[Level1] [varchar](100) NOT NULL,
[Level2] [varchar](100) NOT NULL,
[Level3] [varchar](100) NOT NULL,
[Level4] [varchar](100) NOT NULL,
[Level5] [varchar](100) NOT NULL,
[Level6] [varchar](100) NOT NULL,
[Level7] [varchar](100) NOT NULL,
[Level8] [varchar](100) NOT NULL,
[Level9] [varchar](100) NOT NULL,
[Level10] [varchar](100) NOT NULL,
[Level11] [varchar](100) NOT NULL,
[Level12] [varchar](100) NOT NULL
) ON [PRIMARY]
END ;

DELETE DirectoryTree WHERE Root=@Folder;

-- Recursive CTE
WITH cteDirTree(FolderID, Name, ParentID, Level)
AS(
-- Anchor Member (AM)
SELECT ID, SubDir, ParentID, 0
FROM #DirTree
WHERE ParentID = 0
UNION ALL
-- Recursive Member (RM)
SELECT ID, SubDir, D.ParentID, C.Level+1
FROM #DirTree D
JOIN cteDirTree AS C
ON D.ParentID = C.FolderID)

-- Tree builder from one level to next
INSERT INTO [AdventureWorks].[dbo].[DirectoryTree]([Root],[Level1],[Level2],[Level3],
[Level4],[Level5],[Level6],[Level7],[Level8],[Level9],
[Level10],[Level11],[Level12])
SELECT Root=l0.Name, Level1=isnull(l1.Name, ''),
Level2=isnull(l2.Name,''), Level3=isnull(l3.Name,''), Level4=isnull(l4.Name,''),
Level5=isnull(l5.Name,''),Level6=isnull(l6.Name,''), Level7=isnull(l7.Name,''),
Level8=isnull(l8.Name,''), Level9=isnull(l9.Name,''), Level10=isnull(l10.Name,''),
Level11=isnull(l11.Name,''), Level12=isnull(l12.Name,'')
FROM
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=0) l0
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=1) l1
ON l1.ParentID =l0.FolderID
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=2) l2
ON l2.ParentID = l1.FolderID
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=3) l3
ON l3.ParentID = l2.FolderID
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=4) l4
ON l4.ParentID = l3.FolderID
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=5) l5
ON l5.ParentID = l4.FolderID
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=6) l6
ON l6.ParentID = l5.FolderID
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=7) l7
ON l7.ParentID = l6.FolderID
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=8) l8
ON l8.ParentID = l7.FolderID
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=9) l9
ON l9.ParentID = l8.FolderID
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=10) l10
ON l10.ParentID = l9.FolderID
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=11) l11
ON l11.ParentID = l10.FolderID
LEFT JOIN
(SELECT Name, FolderID, ParentID FROM cteDirTree where Level=12) l12
ON l12.ParentID = l11.FolderID
ORDER BY l1.Name, l2.Name, l3.Name, l4.Name, l5.Name, l6.Name, l7.Name,
l8.Name, l9.Name, l10.Name, l11.Name, l12.Name

SELECT * FROM DirectoryTree WHERE ROOT=@Folder ORDER BY DirectoryTreeID
END
go

EXEC DirTree 'c:\Program Files\'
go

This is the partial result set:

ID	Root	Level1	Level2	Level3	Level4	Level5
25	c:\Program Files\	Adobe	Acrobat 5.0	Reader	Optional
26	c:\Program Files\	Adobe	Acrobat 5.0	Reader	plug_ins	InterTrust
27	c:\Program Files\	Adobe	Acrobat 5.0	Reader	plug_ins	Movie
28	c:\Program Files\	Adobe	Acrobat 5.0	Reader	plug_ins	vdkhome enu
29	c:\Program Files\	Adobe	Acrobat 5.0	Reader	plug_ins	WEBBUY HTML

The fnPath function returns a path based on the directory id in DirectoryTree table:

CREATE FUNCTION fnPath (@DirectoryTreeID int)
RETURNS varchar(2048)
AS
BEGIN
DECLARE @Path varchar(2048)
SELECT @Path=Root+Level1+'\'+Level2+'\'+Level3+'\'+Level4+'\'+
Level5+'\'+Level6+'\'+Level7+'\'+Level8+'\'+Level9+
'\'+Level10+'\'+Level11+'\'+Level12+'\'
FROM dbo.DirectoryTree
WHERE DirectoryTreeID=@DirectoryTreeID
WHILE ( right(@Path,2)='\\' ) SET @Path=left(@Path, len(@Path)-1)
RETURN @Path
END
GO
SELECT dbo.fnPath (10)
GO

The ListFilesInSubfolder stored proc returns the list of files in a subfolder:

CREATE PROC ListFilesInSubfolder @DirectoryTreeID int
AS
BEGIN
DECLARE @Path varchar(2048)
SELECT @Path = 'dir "'+dbo.fnPath ( @DirectoryTreeID)+'" /A-D /B'
CREATE TABLE #FileList ( Line varchar(512))
INSERT #FileList
EXEC master.dbo.xp_cmdshell @Path
SELECT * FROM #FileList WHERE Line is NOT NULL
END
GO

EXEC dbo.ListFilesInSubfolder 200
GO