DECLARE @Path VARCHAR(100)
SET @Path = 'D:\'
-- получение списка файлов 
IF OBJECT_ID('TEMPDB..#dirtree') IS NOT NULL DROP TABLE #dirtree
CREATE TABLE #dirtree(ID int NOT NULL identity(1,1) primary key, subdirectory VARCHAR(256), depth INT, [file] INT)
CREATE INDEX IX_#dirtree ON #dirtree(depth,id)
INSERT INTO #dirtree (subdirectory,depth,[file]) EXEC xp_dirtree @Path, 0, 1

;with alldir(subdirectory,depth,id) as 
(select cast((@Path+subdirectory) as varchar(max)) as subdirectory,depth,id
from #dirtree d1
where [file]=0
and depth = 1
union all
select a.subdirectory+'\'+d1.subdirectory,d1.depth,d1.ID
from alldir a
join #dirtree d1 on d1.depth = a.depth +1 and  d1.id > a.id 
and not exists(select * from #dirtree d2 where d2.[file]=0 and d2.depth = a.depth and d2.ID > d1.id)
) select subdirectory from alldir
--where subdirectory like  'MyFileName'
order by subdirectory

DECLARE @Path VARCHAR(100)
SET @Path = 'C:\'
-- получение списка файлов 
IF OBJECT_ID('TEMPDB..#dirtree') IS NOT NULL DROP TABLE #dirtree
CREATE TABLE #dirtree(ID int NOT NULL identity(1,1) primary key, subdirectory VARCHAR(256), depth INT, [file] INT)
CREATE INDEX IX_#dirtree ON #dirtree(depth,id)
INSERT INTO #dirtree (subdirectory,depth,[file]) EXEC xp_dirtree @Path, 0, 1



declare @fileName varchar(100) 
SET @fileName = 'newindex'


;with fullpath(subdirectory,depth,id) as 
(select  cast(subdirectory as varchar(max)),depth,id
from #dirtree d1
where [file]=1
and subdirectory = @fileName
union all
select d1.subdirectory+'\'+a.subdirectory,d1.depth,d1.ID
from fullpath a
join #dirtree d1 on d1.depth = a.depth -1 and  d1.id < a.id  and d1.[file]=0
and not exists(select * from #dirtree d2 where d2.[file]=0 and d2.depth = d1.depth and d2.ID > d1.id)
) select top 1 @Path+'\' + subdirectory from fullpath
order by depth