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?
x
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:tempabc.mdf
c:tempaa.txt
c:tempab.txt
c:tempa.rb
c:tempaaa.flv
*/
Advertisement
Answer
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:tempaa.txt
c:tempab.txt
c:tempabc.mdf
c:tempa.rb
c:tempaaa.flv
EDIT
Changed the tables used in the example to more look like the ones in your question