DECLARE @Path nvarchar(260) SET @Path = 'E:/study/SQL' 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 #