Use SQL Server recursive common table expression to get full path of all files in a folder(with subfolders)

common-table-expression, recursion, sql, sql-server

Solution

If I understand you correct you want something like this:

Test data:

CREATE TABLE #dirtree
(
    id INT,
    subdirectory NVARCHAR(260),
    depth INT ,
    is_file BIT,
    parentId INT
)

INSERT INTO #dirtree(id,subdirectory,depth,is_file)
VALUES
    (1,'abc.mdf',1,1),(2,'a',1,0),(3,'a.txt',2,1),
    (4,'b.txt',2,1),(5,'a.rb',1,1),(6,'aaa.flv',1,1)

Updated the parent id

UPDATE #dirtree
SET ParentId = (SELECT MAX(Id) FROM #dirtree
      WHERE Depth = T1.Depth - 1 AND Id < T1.Id)
FROM #dirtree T1

Query

;WITH CTE
AS
(
    SELECT
        t.id,
        t.subdirectory,
        t.depth,
        t.is_file
    FROM
        #dirtree AS t
    WHERE
        is_file=0
    UNION ALL
    SELECT
        t.id,
        CAST(CTE.subdirectory+'\'+t.subdirectory AS NVARCHAR(260)),
        t.depth,
        t.is_file
    FROM
        #dirtree AS t
        JOIN CTE
            ON CTE.id=t.parentId
    )
SELECT
    'c:\temp\'+CTE.subdirectory AS [path]
FROM
    CTE
WHERE
    CTE.is_file=1
UNION ALL
SELECT
    'c:\temp\'+t.subdirectory
FROM
    #dirtree AS t
WHERE
    is_file=1
    AND NOT EXISTS
    (
        SELECT
            NULL
        FROM
            CTE
        WHERE
            CTE.id=t.id
    )

Result

path
---------------
c:\temp\a\a.txt
c:\temp\a\b.txt
c:\temp\abc.mdf
c:\temp\a.rb
c:\temp\aaa.flv

EDIT

Changed the tables used in the example to more look like the ones in your question

Problem

There is a SQL Server undocumented extended stored procedure called `xp_dirtree`, which can return all files and folders name (include subfolders) in a table format. To practice my understanding of recursive CTE, I decide to use it to get the full path of all files in a specified folder(include subfolders). However, after an hour of head scratch I still can't figure out the correct way to do it. The following code is what I have currently. Can this purpose be implemented with recursive CTE? ``` DECLARE @dir NVARCHAR(260) ; SELECT @dir = N'c:\temp' ; IF RIGHT(@dir, 1) <> '\' SELECT @dir = @dir + '\' ; IF OBJECT_ID('tempdb..#dirtree', 'U') IS NOT NULL DROP TABLE #dirtree ; CREATE TABLE #dirtree ( id INT PRIMARY KEY IDENTITY, subdirectory NVARCHAR(260), depth INT, is_file BIT ) ; INSERT INTO #dirtree EXEC xp_dirtree @dir, 0, 1 ; SELECT * FROM #dirtree ; WITH files AS ( SELECT id, subdirectory, depth, is_file, subdirectory AS path FROM #dirtree WHERE is_file = 1 AND depth <> 1 UNION ALL -- ... ) SELECT * FROM files ; ``` Suppose the xp_dirtree output is: ``` /* id subdirectory depth is_file --- -------------- ------- ------- 1 abc.mdf 1 1 2 a 1 0 3 a.txt 2 1 4 b.txt 2 1 5 a.rb 1 1 6 aaa.flv 1 1 */ ``` What I want is: ``` /* path ------------------ c:\temp\abc.mdf c:\temp\a\a.txt c:\temp\a\b.txt c:\temp\a.rb c:\temp\aaa.flv */ ```

Original source