SQL遍历文件夹，返回文件夹列表信息

DECLARE @Path nvarchar(260)
SET @Path = 'C:\Downloads'

IF RIGHT(@Path, 1) <> '\'
    SET @Path = @Path + '\'
IF OBJECT_ID('tempdb..#') IS NOT NULL
    DROP TABLE #
CREATE TABLE #(
    id int IDENTITY,  --编号
    directory nvarchar(260),  --路径
    depth int, --深度，相对与@path
    IsFile bit )--0文件夹1文件名成
INSERT # EXEC master.dbo.xp_dirtree 
    @path = @path,
    @depth = 0,
    @file = 1

DECLARE @depth int, @depthMax int
UPDATE # SET 
    directory = @Path + directory
WHERE depth = 1
SELECT 
    @depth = 2,
    @depthMax = MAX(depth)
FROM #
WHILE @depth <= @depthMax
BEGIN
    UPDATE A SET 
        directory = (
                SELECT TOP 1 
                    directory
                FROM #
                WHERE depth = @depth - 1
                    AND IsFile = 0
                    AND id < A.id
                ORDER BY id DESC
            ) + N'\' + directory
    FROM # A
    WHERE depth = @depth
    SET @depth= @depth + 1
END
SELECT * FROM #