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 #

declare @dir nvarchar(260)
declare youbiao cursor for
select directory from #
open youbiao
fetch next from youbiao into @dir
while @@fetch_status=0
begin
 print @dir
fetch next from youbiao into @dir
end
close youbiao
deallocate youbiao