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 @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 #