标题: 快速度查找文件和目录的SQL语句 [打印本页] 作者: admin 时间: 2012-9-15 14:42 标题: 快速度查找文件和目录的SQL语句 查找文件的语句 3 U/ u% g& m" Q& F7 h0 c& x y+ r9 x( C% U' _8 S8 P
CODE:. [0 ]' K2 \' X) F1 W: c- m
0 n t$ @- f9 M6 k, }
drop table tmp; ) _% F+ [; D& z! [create table tmp 9 Q% g* t! N ?* S(. P9 z; W" K* b
[id] [int] IDENTITY (1,1) NOT NULL,0 j R& e8 B! f L0 u1 R! N: N
[name] [nvarchar] (300) NOT NULL, ' h- ~6 j* e1 v) g" S[depth] [int] NOT NULL,/ f9 a. H4 F/ M+ |/ M
[isfile] [nvarchar] (50) NULL2 p( R: C L; o% `+ R
);& N; L! Y7 t! T0 K1 i
Q) x+ M6 S- Y- |6 g6 P% v, B/ C. Ideclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)2 k- E. a; h' X3 N/ l$ V
set @root='f:\usr\' -- Start root ) z# s" R# K9 K* V5 H; @set @name='cmd.exe' -- Find file + s, o- F% E2 F3 g7 d) e2 vinsert into tmp exec master..xp_dirtree @root,0,1-- ; [5 z. D3 W: M% A; C) fset @id=(select top 1 id from tmp where isfile=1 and name=@name) 5 x5 _6 { S. b
set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)5 ^: ?8 K' x ~% n# t5 S
while @depth<>1 ) @' J9 U/ W M
begin 2 w% K3 O: o+ S
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) - w9 B( w; X; @0 t% ~+ }set @depth=(select depth from tmp where id=@id) ; Y! T) r: z5 tset @name=(select name from tmp where id=@id)+'\'+@name9 l; v' I5 e2 W6 c$ R3 J4 y# d7 z6 @0 q$ O
end : a! o& q v, z7 t4 lupdate tmp set name=@root+@name where id=18 e* ?$ i0 W/ x) k% x! p
select name from tmp where id=1 ) ~$ V! Z' [* s- [7 o" T5 f7 ?7 |* d; w5 ?5 }
查找目录的语句 + |; b: e) W* U% c- n 6 s7 F& Y# T1 `. z9 S* U2 ]& u ?) ]) ]# {, m7 Y% v; C
CODE: , R5 d" {9 x6 i, ]0 `3 H8 v& F7 ]2 q8 O
( c- o2 k" _$ q7 h9 c
drop table tmp; % C8 H; n6 Z3 i) kcreate table tmp ) p& @. |# K$ b3 R- L0 o( - w1 d# d: F# m$ t[id] [int] IDENTITY (1,1) NOT NULL, ^2 o5 V7 S u2 H& L, i[name] [nvarchar] (300) NOT NULL, ( b9 T: g q' ^: R[depth] [int] NOT NULL2 j' X+ L9 @! q6 ?$ k
);8 d+ N+ ?8 O$ i- a' _1 x
- \' ?- R8 c# l$ Edeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300) * r& I- P2 R% Oset @root='f:\usr\' -- Start root * r# F; D9 z9 y8 @9 P0 R4 b/ cset @name='donggeer' -- directory to find# S/ N/ U& f6 w- Y& t( c
insert into tmp exec master..xp_dirtree @root,0,0 ) M5 F2 [, E" l4 W4 W5 H% e' ?2 Oset @id=(select top 1 id from tmp where name=@name) # _& Y) s4 k' i7 \8 r/ V( Gset @depth=(select top 1 depth from tmp where name=@name) ' q# U5 l/ B8 y7 G: @1 C' dwhile @depth<>1 + i' i9 O B/ s X! Z- u
begin 4 K7 T6 F; O( N5 p1 g. |set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)" J; b' _/ _! M- M, L5 z9 v
set @depth=(select depth from tmp where id=@id) , ~1 U; M3 s+ J: g0 @+ j1 V
set @name=(select name from tmp where id=@id)+'\'+@name ) G0 V* x' T* E1 }! N0 I1 [end update tmp set name=@root+@name where id=1 : @& M A- j$ D$ Z( \% Jselect name from tmp where id=1