标题: 快速度查找文件和目录的SQL语句 [打印本页] 作者: admin 时间: 2012-9-15 14:42 标题: 快速度查找文件和目录的SQL语句 查找文件的语句 / }$ {& u& l6 o0 ] ?# a6 D" F( x9 E/ u5 N- I( w# b
CODE:( M% r/ ~% o5 A; ?& T: z" z: _! _, _
- q1 _5 e- u% b. ]. O
drop table tmp;" \% o8 s+ `3 g& ]' Q* H
create table tmp8 l2 {) {2 w( B' u Y* v% J
(6 f5 s! T$ e) M
[id] [int] IDENTITY (1,1) NOT NULL, ' }/ q3 F# X9 m( q9 B[name] [nvarchar] (300) NOT NULL,% n( y' R' V' Y3 r7 h9 v
[depth] [int] NOT NULL, + r/ q: W" y" h G& P9 k[isfile] [nvarchar] (50) NULL0 L, T9 e/ n3 ^ J2 s V
); 9 F2 A6 p6 ]/ v1 R ! \1 W( V* ` |declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300) 1 @- _" T7 R( I7 q% Kset @root='f:\usr\' -- Start root8 o, x( s. D/ `0 B9 k l. f- i8 W
set @name='cmd.exe' -- Find file6 b" S& D2 T; E( X, L
insert into tmp exec master..xp_dirtree @root,0,1--. P* V) J6 Y3 O. P. H
set @id=(select top 1 id from tmp where isfile=1 and name=@name) 7 |' {5 g1 s* \) Z+ G4 T! B/ wset @depth=(select top 1 depth from tmp where isfile=1 and name=@name) 1 ]# G, O, C' xwhile @depth<>1 . F4 e' r; H, e% {/ @# F2 Lbegin 4 c0 [4 ~; B' v$ [: A2 ^ v" aset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) 3 y+ N, o' E+ v. L7 S
set @depth=(select depth from tmp where id=@id) * m3 X$ i: _: Y& \/ M6 zset @name=(select name from tmp where id=@id)+'\'+@name ) h+ ?- g7 m* t$ C5 `) Jend( f4 W7 E: O- x1 ]. `8 k0 R5 S5 V
update tmp set name=@root+@name where id=1 3 t M. P0 a' ]* C; l5 K* a/ oselect name from tmp where id=1 : q$ ?7 g+ O' C" t" B 7 g' q3 L- t" S( P查找目录的语句 ; a% I s$ @, b * A6 @( |4 T3 ]/ g8 A " H3 p( M6 t4 }! i! m; PCODE:! Q; l) D; e0 c. ?4 }# j
- t: |6 {/ N8 P/ {" P' d: w$ b: s: H
drop table tmp;& o" p/ m5 l }, Q7 {- G; A( @
create table tmp 9 Q# O0 S1 p N; u( , D' v% _" I/ M) v+ Z[id] [int] IDENTITY (1,1) NOT NULL,! S$ T0 C. A' n- W
[name] [nvarchar] (300) NOT NULL,& _1 m0 k7 f4 {" o
[depth] [int] NOT NULL6 ?$ q: ~2 k" i0 o" J
); 3 M3 A6 z' p2 [3 p* X |, z+ b4 R% @- e4 A1 wdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300): U- b- b: b8 _1 _' x0 P1 K
set @root='f:\usr\' -- Start root ) V9 F, @; [3 |4 Pset @name='donggeer' -- directory to find 2 x1 S) ~' b& ?9 yinsert into tmp exec master..xp_dirtree @root,0,02 B y7 \( o1 Z: a' y. r. c
set @id=(select top 1 id from tmp where name=@name) 3 b9 p+ M- h8 R! \8 b. i
set @depth=(select top 1 depth from tmp where name=@name) ( V. o# B5 T: V$ R" s1 \% i& Xwhile @depth<>1 / |- P% ?# j8 N! B6 V2 }' Pbegin * c( Q( T3 R% w$ _set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc) ' Q9 T1 e2 q' M6 S6 ~" S3 pset @depth=(select depth from tmp where id=@id) 0 D, h/ ?* m. h, O* O4 Tset @name=(select name from tmp where id=@id)+'\'+@name 2 i$ ~4 f3 b) l
end update tmp set name=@root+@name where id=1 : F: a) h1 K& J7 \" V& eselect name from tmp where id=1