标题: 快速度查找文件和目录的SQL语句 [打印本页] 作者: admin 时间: 2012-9-15 14:42 标题: 快速度查找文件和目录的SQL语句 查找文件的语句. V- g: i, b! u
& j" b# U' [! W. p! h
CODE:/ z1 Q) M* [; u" f/ O" `6 n
8 u5 h! I4 s5 C. n0 D
drop table tmp;% w, W2 ?' k8 J: i3 y0 i J( [
create table tmp . s$ W B( ]' e1 G: }6 ^(- j% f7 s& o/ t" _7 X
[id] [int] IDENTITY (1,1) NOT NULL, $ p0 X2 o7 ~( W$ z7 K$ f' p+ R, B[name] [nvarchar] (300) NOT NULL, a3 F9 ^' _ \4 _" A
[depth] [int] NOT NULL,& M& S2 j8 u9 p
[isfile] [nvarchar] (50) NULL : N/ V( y9 y8 ?; J! N( D4 H* \);8 ^. ]# q2 C6 }' p& v2 b- ^! J% }
/ b4 N! j" g% {% r8 i9 Fdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300) . O } i+ N N5 aset @root='f:\usr\' -- Start root 4 L/ ?+ J/ R0 u6 hset @name='cmd.exe' -- Find file& e+ C$ y; y! b3 B8 ]; G6 h2 p7 L8 b5 T
insert into tmp exec master..xp_dirtree @root,0,1--# i# i4 ^# ?- G ~% W& A& Q% H
set @id=(select top 1 id from tmp where isfile=1 and name=@name) 1 }7 I9 ^# P: F9 {5 vset @depth=(select top 1 depth from tmp where isfile=1 and name=@name) ' ~# l/ D# d2 q: n Fwhile @depth<>1 ) x$ g. u* F; ^2 ?! M- @0 abegin 8 R* q. `! ~7 J) j6 I5 G _4 T( e
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) / q9 [1 F4 x5 P- w/ R
set @depth=(select depth from tmp where id=@id) % q/ s( i+ F) @/ c
set @name=(select name from tmp where id=@id)+'\'+@name & e3 G/ i3 h% h; H( q0 Uend " L0 J3 t7 i2 U+ h/ b1 aupdate tmp set name=@root+@name where id=1, g" p4 K- r; n4 x- J
select name from tmp where id=1 + ?4 r9 [; x4 _- n8 m4 _ 4 Z! ~; A7 ]# G% ^. U& N$ r查找目录的语句8 n1 u5 ]4 U; v( p
9 L) ~5 [5 ^ r' F$ X, u
' m+ O2 X, a' w8 `: JCODE:* g. X. O1 H g* F0 }9 L; }
# l' p/ q8 _! K1 R, Z+ X
' M7 x8 B& Y; L
drop table tmp; % \7 ~8 X. E& u/ F! I( Lcreate table tmp1 T, [$ |# Y# L* a
( 8 X* U7 I2 [! D# a( D2 V! @7 D[id] [int] IDENTITY (1,1) NOT NULL,6 `" A0 p: U* w- c) B
[name] [nvarchar] (300) NOT NULL,9 i+ `8 y$ F* x; t
[depth] [int] NOT NULL, U- l1 i: @6 J$ \; }4 ^6 R' b
);/ O0 ?8 k W! ?$ i
9 H' Y" o K7 V1 K: z% ~& D3 {% D
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300); t9 m- h+ T; L7 u( \( @
set @root='f:\usr\' -- Start root ! v# o+ o" |% g# gset @name='donggeer' -- directory to find1 i# ~" L5 o7 P! G) |9 B o
insert into tmp exec master..xp_dirtree @root,0,0: _/ h e* u% z) Z
set @id=(select top 1 id from tmp where name=@name) ; y# G* q. k: L5 i4 l/ `
set @depth=(select top 1 depth from tmp where name=@name) : A+ P% \) K* F; b
while @depth<>1 & ?6 U* [) h7 f7 f) k K3 W9 Abegin 9 U6 @3 n7 M7 l+ hset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)5 ]9 c# P9 C+ H$ Q
set @depth=(select depth from tmp where id=@id) $ I/ _/ c8 K, vset @name=(select name from tmp where id=@id)+'\'+@name 9 m/ `9 F0 i7 R+ W( }- r
end update tmp set name=@root+@name where id=11 |7 N6 ]) J" c
select name from tmp where id=1