标题: 快速度查找文件和目录的SQL语句 [打印本页] 作者: admin 时间: 2012-9-15 14:42 标题: 快速度查找文件和目录的SQL语句 查找文件的语句- F% r3 w# Y/ Z8 a$ W n* ~ Y5 K
/ ^ a8 W. x& w i# m g5 x
CODE: ) C8 Q" m$ o& o" e+ U0 z5 [8 P& G1 g
drop table tmp; 8 A4 Q$ E$ L. _, n- F4 ? q* rcreate table tmp$ i, Q U5 A0 O% }& f3 I1 E
(% }3 ]! C! k' j
[id] [int] IDENTITY (1,1) NOT NULL," @5 R7 }- x. u$ F% Q, {8 x
[name] [nvarchar] (300) NOT NULL, $ Q& c7 l) W6 r8 {* Q/ n% b[depth] [int] NOT NULL, 8 Z: k! x& m7 [5 w! f& @+ @[isfile] [nvarchar] (50) NULL . V# p- v. w$ w/ p); / i. s0 Y- ]8 C4 }0 [- [. j # g- u6 i+ Q6 A. v/ i' Z9 xdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300) 8 ~1 w" Q( q$ t% p7 i9 F5 O( r( kset @root='f:\usr\' -- Start root ; G1 E" o) O+ m( Q& ^' ?: @set @name='cmd.exe' -- Find file 4 m" K" w; u+ T: u0 F7 W tinsert into tmp exec master..xp_dirtree @root,0,1-- / R4 U! r) g3 j% Oset @id=(select top 1 id from tmp where isfile=1 and name=@name) 9 W8 c0 m* J' j6 tset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)# ]# I3 x; m- [* Q3 K1 N' l; W
while @depth<>1 / m2 u* q' V8 f& j- E5 S9 f! u
begin 5 x9 H6 m. T7 N6 G; T0 t1 c' G
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) * ]2 d7 W1 }9 D8 D! f6 M9 p4 Y5 u+ Iset @depth=(select depth from tmp where id=@id) 9 {5 p+ \3 ~2 u jset @name=(select name from tmp where id=@id)+'\'+@name 5 E; I! i# K5 A2 Uend ' c3 ~3 f% {, \% d8 y% e1 g) xupdate tmp set name=@root+@name where id=1, [( {# b$ v2 c/ p' v" R' j/ R
select name from tmp where id=1: k" j/ I* k+ O4 t T
0 r$ [+ B1 m0 [ a5 T4 _. y! I
查找目录的语句4 O: t9 x6 O# M4 q5 c
: x0 B; y+ f9 }9 }& D" F
9 h( E, g( ?: k! [! q9 k- s
CODE:9 d- B" K# r# U1 o q5 q
. {; x" H; e: z2 e9 T( Q, d; Z# g2 a. T4 B9 n. p8 |. l9 f
drop table tmp; 0 L7 I5 e4 S1 q* M$ t4 Gcreate table tmp% ~4 ]+ ?$ _9 b/ `
( " r* t8 |) b* f: r% c7 ~[id] [int] IDENTITY (1,1) NOT NULL,+ y" n$ Y* u) \( a$ s
[name] [nvarchar] (300) NOT NULL,7 Y/ E3 M/ S, ~
[depth] [int] NOT NULL / ~. g7 k) d. ]5 I);* T- |6 B$ X. K( V) a. B
2 ?" Y5 \$ `4 E* Kdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300) ; e) b! @. M0 ^9 Z5 F' u* v) m# ]0 v. R- Rset @root='f:\usr\' -- Start root 0 [. N# A$ V" r _2 `, V& C. Wset @name='donggeer' -- directory to find % _) N( `+ ?; ]/ h s Iinsert into tmp exec master..xp_dirtree @root,0,0/ J0 }3 ?2 ]& H( D: f
set @id=(select top 1 id from tmp where name=@name) ; l: @1 P l6 `; @7 U: M: F3 @0 T
set @depth=(select top 1 depth from tmp where name=@name) 4 x+ r2 X% s: Swhile @depth<>1 * J3 M# {6 G. ]4 X/ bbegin 5 T* v1 P: v. u/ t
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)6 t7 V4 V! [# ~7 W: W+ J& }5 b* i
set @depth=(select depth from tmp where id=@id) 6 `3 _6 T3 n4 _
set @name=(select name from tmp where id=@id)+'\'+@name 5 Q, j. ~2 D& F/ ~* Qend update tmp set name=@root+@name where id=1: p0 R! x) D4 F. s
select name from tmp where id=1