- M+ V: r( g8 @" Mdrop table tmp; 1 Z( |' U' @2 F" ocreate table tmp% }" B0 L4 J1 j7 [* D! [: W! l
(1 u' s$ I, D- ?; i6 D
[id] [int] IDENTITY (1,1) NOT NULL, 6 l2 j5 T) Y: e' _' N+ @$ O% i[name] [nvarchar] (300) NOT NULL, * N* \) c4 x: U8 {' @3 z[depth] [int] NOT NULL,0 f q0 Q t7 o8 B
[isfile] [nvarchar] (50) NULL 9 W J5 h$ u; |* e7 ^% j2 l/ ]);& H$ D0 r/ `6 W( ?& z5 I" b
9 y2 m! |8 n* U( s, X6 K+ N" Q
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300) 3 V% V* D3 [! Q0 Dset @root='f:\usr\' -- Start root " F. [$ P( p* R+ L6 i- `% xset @name='cmd.exe' -- Find file+ V" A0 I) D( j% L2 E
insert into tmp exec master..xp_dirtree @root,0,1--! w | m( E% i! F, [
set @id=(select top 1 id from tmp where isfile=1 and name=@name) - D7 N' A. ]1 X# o! H
set @depth=(select top 1 depth from tmp where isfile=1 and name=@name) " q% h( b+ Q5 g$ E1 l7 K) W* Jwhile @depth<>1 ! m1 Z' Z3 @7 `9 @1 x* r, }
begin ! r9 {8 A- l. U0 S7 `: q' h
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) 1 N: Q- g0 b# F7 O1 r
set @depth=(select depth from tmp where id=@id) * c. T/ V4 x3 z8 Eset @name=(select name from tmp where id=@id)+'\'+@name 2 f- y- l- K: n, ]end' v6 E" i: P$ P% Q' j
update tmp set name=@root+@name where id=17 G9 T$ |8 {8 u4 q- @0 Q
select name from tmp where id=11 N ~9 s9 B8 S
" ^9 _3 I; @" g) H! t8 _查找目录的语句' O) D. E R1 p* U
# P( c: x) x# V. A' F, E9 K2 P" O. A- O! P# V, d4 W
CODE: ' O* S+ P9 B9 M* o% }; l/ M; q$ X/ C+ p, Z! l: `
8 i/ e, Y6 _( E; x& F0 Odrop table tmp;* A7 {3 I4 W. N+ O2 e9 R/ L M
create table tmp % D% @/ J" e! [& ~7 a5 \8 L; O( : p+ w2 H) v* l1 Z" Q[id] [int] IDENTITY (1,1) NOT NULL,- y' T3 _6 u$ U9 p5 ~- X6 C: K
[name] [nvarchar] (300) NOT NULL,* T4 c8 D7 d r8 ?$ L
[depth] [int] NOT NULL * P* U$ Q: |* D0 y4 L* m8 o( e);# i% g: b! Y* {1 p+ U
+ Q1 P/ b F! B
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300); w0 o {- M2 Z5 w! @5 ~( @
set @root='f:\usr\' -- Start root * g+ f( h: A9 r2 A9 qset @name='donggeer' -- directory to find / K, s/ c7 {5 W! Xinsert into tmp exec master..xp_dirtree @root,0,0 & U* P% r& i9 P) ~1 Z y0 Zset @id=(select top 1 id from tmp where name=@name) + h/ t. u4 t4 Z4 L5 W2 f* jset @depth=(select top 1 depth from tmp where name=@name) ' E+ g1 w( t, v8 v7 O$ s0 L
while @depth<>1 ' g% n% @3 o6 x O
begin 6 v6 E ?) j: V1 `3 Cset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)( w. g) t# `+ x4 `# a3 {# _# G
set @depth=(select depth from tmp where id=@id) 7 ` w9 R9 v9 k2 ?4 n) z# \$ Xset @name=(select name from tmp where id=@id)+'\'+@name : M E! F/ W. N# [- A9 bend update tmp set name=@root+@name where id=1" r; k3 o i8 E
select name from tmp where id=1