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 #

 

转载于:https://www.cnblogs.com/554006164/archive/2011/11/04/2236211.html