: I% u( o; X+ t vdrop table tmp; + H+ H- g% e: [) R/ Lcreate table tmp* Y. l: t! }8 N: p" b# Y
(. _( _' `4 b+ ] Y3 b8 H/ n
[id] [int] IDENTITY (1,1) NOT NULL,: y: M ^+ ]2 {6 V" x1 J
[name] [nvarchar] (300) NOT NULL, 7 o& s+ j' ], R0 H8 u[depth] [int] NOT NULL, " i- G, ~9 z4 m9 m/ p[isfile] [nvarchar] (50) NULL 9 E3 ^$ O8 H" C: S. D);) X! X/ M0 e3 T4 ?7 N
) q/ X! P# _! v$ R A
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300) 2 n a$ F/ j. w6 |set @root='f:\usr\' -- Start root 2 g2 ~0 w N" P7 G: g0 qset @name='cmd.exe' -- Find file) x# i7 L$ b( m9 K
insert into tmp exec master..xp_dirtree @root,0,1--4 m7 _5 O T! y
set @id=(select top 1 id from tmp where isfile=1 and name=@name) # @; T% C0 b5 W; _4 ^6 u( eset @depth=(select top 1 depth from tmp where isfile=1 and name=@name) ; `% p# |* @* L5 g+ u9 B1 lwhile @depth<>1 8 G/ W3 E1 y7 U
begin 2 Y) N5 @9 k9 A
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) + r Y+ N# M- N4 x6 ~" Eset @depth=(select depth from tmp where id=@id) + i) H/ N+ x- M7 Q% E( n) V( O
set @name=(select name from tmp where id=@id)+'\'+@name # ^- }3 }* K; i/ z9 r, B3 cend+ M0 `" ]% ^8 H# Q! r* y
update tmp set name=@root+@name where id=1 , ?+ h: h3 n' Q1 B3 Qselect name from tmp where id=1 ( C2 H! s* j% v$ R" A% ` , B G' {) S7 Z' y7 z( M* h' W查找目录的语句3 v4 ^' ~, q: @( O
" ~/ a% F( Y* q5 z g
2 ~8 q* b1 h: X4 A8 b3 G
CODE:* Q% `6 c* G* Y6 u
. X. ?5 f/ V. g* G4 N9 h2 f! a( o0 h6 r! e4 d. j$ p
drop table tmp;5 ]) O p$ ~5 }* C
create table tmp8 N& h6 y, e: p, D! U, A
($ p' r [7 X7 C7 X. z
[id] [int] IDENTITY (1,1) NOT NULL,* @ a. R+ H0 b$ b9 o+ h) ~
[name] [nvarchar] (300) NOT NULL, % L6 h1 D. c6 d2 A5 P[depth] [int] NOT NULL / E9 t) r5 N7 L( L, F); \% b+ u* O8 F% v, I' c$ t; d$ _3 r ! G$ w8 Z9 _8 N s; t8 _declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300) A4 t) ?& ], C ~- F4 p+ Y
set @root='f:\usr\' -- Start root : e4 Z2 v8 M1 x7 U2 t0 N( bset @name='donggeer' -- directory to find2 v% O, g8 Y/ [4 ]; P- u9 B
insert into tmp exec master..xp_dirtree @root,0,0" l9 s: l* D; R* Q) k% \4 h( P
set @id=(select top 1 id from tmp where name=@name) % o8 X! x) b, c+ e$ hset @depth=(select top 1 depth from tmp where name=@name) 1 f$ I4 Q* `; s& e; l3 g
while @depth<>1 8 c3 t1 Y4 ~$ h1 g0 `4 ibegin 6 q5 T8 w; _+ w! P
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc) # W* G' Z3 m6 y4 `, qset @depth=(select depth from tmp where id=@id) . i) b/ g+ s2 G8 Jset @name=(select name from tmp where id=@id)+'\'+@name 8 m+ K- v+ d% a% l5 z
end update tmp set name=@root+@name where id=1 9 z! `' {1 O" B% C# m2 Aselect name from tmp where id=1