: Z n/ Q% [8 b* MCODE: 9 ^8 K# p7 Y9 W' ]( \' N$ P+ j; {* D% w. `5 K
drop table tmp;' c; B' k' O( k/ C& `; T1 T
create table tmp 8 [$ p; a: a0 f" ^(6 t x7 b. L: v+ p! k% }: a3 K/ ~* L
[id] [int] IDENTITY (1,1) NOT NULL, ' |4 m! |2 Z Z[name] [nvarchar] (300) NOT NULL,% s3 Y- A5 T) Q) ?# F8 ~+ n
[depth] [int] NOT NULL,5 ?8 L& d3 J5 }7 H
[isfile] [nvarchar] (50) NULL$ S, }* L6 [6 k! m. r
); 8 X$ E2 D3 ~4 t5 D2 E' j8 S% \+ }- s" n7 R8 D* J
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)% c8 f. o) g3 `
set @root='f:\usr\' -- Start root7 ^ y, L6 a: q5 g
set @name='cmd.exe' -- Find file1 I' Y% `$ x9 S. S" b! t2 n6 u/ F
insert into tmp exec master..xp_dirtree @root,0,1--- { ~/ y, W) @& X7 m
set @id=(select top 1 id from tmp where isfile=1 and name=@name) % y7 R( r1 |4 r- q
set @depth=(select top 1 depth from tmp where isfile=1 and name=@name) + T8 A' V) y5 Jwhile @depth<>1 % L2 N" O9 T; t$ M* J/ g, rbegin 3 B; S* j- l& L2 m* W O1 t3 F8 lset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) 6 v+ I5 }3 h' n8 S- mset @depth=(select depth from tmp where id=@id) * R( z% D9 v8 ]set @name=(select name from tmp where id=@id)+'\'+@name! V! U. V, l" H" E9 }. z
end / o( a4 B! O, t7 P* Iupdate tmp set name=@root+@name where id=1 9 u1 L5 u! l+ v$ v3 ?6 F! \. H8 T4 g/ }8 `select name from tmp where id=1 . V) Z8 i s; P& Q: Q# s* T2 _" |. V3 I* t! O+ T1 v
查找目录的语句; a. }6 o' u: s9 u' h
M9 R1 I/ m- |" b9 R; {! ? 6 L$ I) ~7 h& i X6 u2 ]2 ]CODE:, ~4 T. [, h, e6 `# {
0 h/ W/ b% _: }) P0 Y
% `0 \9 i" b; V1 I; o
drop table tmp; : l# X* g7 z: E2 ^. P2 tcreate table tmp ) a. I# v: V( w7 q9 D. d( C" Y0 U- ~6 ?+ G[id] [int] IDENTITY (1,1) NOT NULL,' ]' @5 D$ O3 z( O: `
[name] [nvarchar] (300) NOT NULL, ; N# X4 s. u- B h. c" O! b9 v# m[depth] [int] NOT NULL4 I& W# [1 a" v+ _0 ~9 A
); 2 @+ [$ ?7 x; R . B' S0 ?* Z6 ]0 @( |declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300) : s* Y+ z( z. ^; M- hset @root='f:\usr\' -- Start root% |$ ^+ ~1 `9 d# F r
set @name='donggeer' -- directory to find ( G1 p# D6 S& }. cinsert into tmp exec master..xp_dirtree @root,0,0 C8 l/ j) |2 L2 _& Z0 N
set @id=(select top 1 id from tmp where name=@name) * m* @) j: T. j; N8 Z ~
set @depth=(select top 1 depth from tmp where name=@name) " I5 S# _. X3 R* o$ y/ O& }while @depth<>1 - @5 l& l, e8 ?+ r, Q
begin 4 p' D T( [6 b' }( fset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc) Q$ k- K' u% \* O
set @depth=(select depth from tmp where id=@id) ' V5 v, A# q- b& x: v1 v5 ]1 M |
set @name=(select name from tmp where id=@id)+'\'+@name : I( j8 ]" k2 |" K* Z
end update tmp set name=@root+@name where id=18 T5 w# Q0 Y, {- d
select name from tmp where id=1