exec master..xp_dirtree @path='D:\目录',@depth=0,@file=1--参数:目录名,目录深度,是否显示文件
现在想得到'D:\笨狼代码收藏集'目录下的全部深度的文件路径[含子目录]，如下表：
id[编号] dir [全部文件全路径]
1 D:\笨狼代码收藏集\根目录下文件1.txt
2 D:\笨狼代码收藏集\算法\算法目录下文件1.txt
3 D:\笨狼代码收藏集\算法\算法目录下文件2.txt
4 D:\笨狼代码收藏集\其他\其他目录下文件1.txt
5 D:\笨狼代码收藏集\其他\xml\xml目录下文件1.txt
。。。。。
可以用递归和非递归，方法不限。
谢谢！！~~
15 个解决方案
#1
sql server2000
#2
累了，这东西研究了2小时
#3
关注
#4
关于树型遍历我的blog上有，也许会给楼主启发
#5
还是没写出来，郁闷ing...
#6
到这里领分！
#7
DECLARE @Path nvarchar(260)
SET @Path = 'f:\'
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,
IsFile bit)
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 #
SET @Path = 'f:\'
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,
IsFile bit)
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 #
#8
怎么我一执行，啥都没有？
#9
老大，强！
#10
不用猜了，是我的人品问题……
#11
好贴，收藏
#12
邹老大果然够强！
#13
邹老大强就一个字
#14
邹老大 历害!!!!!!!!!11
#15
xp_dirtree 被干掉了怎么办？
#1
sql server2000
#2
累了，这东西研究了2小时
#3
关注
#4
关于树型遍历我的blog上有，也许会给楼主启发
#5
还是没写出来，郁闷ing...
#6
到这里领分！
#7
DECLARE @Path nvarchar(260)
SET @Path = 'f:\'
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,
IsFile bit)
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 #
SET @Path = 'f:\'
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,
IsFile bit)
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 #
#8
怎么我一执行，啥都没有？
#9
老大，强！
#10
不用猜了，是我的人品问题……
#11
好贴，收藏
#12
邹老大果然够强！
#13
邹老大强就一个字
#14
邹老大 历害!!!!!!!!!11
#15
xp_dirtree 被干掉了怎么办？