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 #

posted on 2006-10-26 18:07  Relance  阅读(275)  评论(0编辑  收藏  举报