标题: 快速度查找文件和目录的SQL语句 [打印本页] 作者: admin 时间: 2012-9-15 14:42 标题: 快速度查找文件和目录的SQL语句 查找文件的语句 - @# v, }$ z2 E4 ^4 c1 C R7 V# t" k; g: r8 h' g( N, {' i9 }, _) j
CODE: 3 u7 f9 t5 J; F8 K1 y" @1 O4 P/ z- T0 b! e5 s2 y! A, O+ a
drop table tmp;, C x! ^/ Z3 r+ H/ C/ Y) Q
create table tmp 0 G) j9 Q8 G2 n( X4 @" N$ m($ [0 D0 o7 p5 N& M% H8 T( j2 T
[id] [int] IDENTITY (1,1) NOT NULL,( l$ c8 l" p v
[name] [nvarchar] (300) NOT NULL,7 x. H" N0 \* F5 n* O. k
[depth] [int] NOT NULL, & U) y( R1 V, |) Z[isfile] [nvarchar] (50) NULL" O4 m3 y3 O, h# z) [) ]
);8 Q- W& w, r9 w2 @
# M# y4 V# Z" N/ w- y& Hdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300) ! O ]0 l1 S3 t0 F: N$ sset @root='f:\usr\' -- Start root , @" ^: J0 ?$ B& sset @name='cmd.exe' -- Find file % A+ H( [8 O; e& p1 Ainsert into tmp exec master..xp_dirtree @root,0,1--1 ^- b8 v. [! ^" u: |# F, R
set @id=(select top 1 id from tmp where isfile=1 and name=@name) " t- H5 w4 ?9 a* Q: aset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)# V5 v" Q3 F0 {& b# b- u% G
while @depth<>1 0 A: x- h8 K1 \! u$ \begin $ ~0 y: R! R) C1 }9 Eset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) % F9 T; @! e% E) aset @depth=(select depth from tmp where id=@id) 7 T+ }+ v# F& n. h9 j3 e) }set @name=(select name from tmp where id=@id)+'\'+@name9 ^, \. s: m& s0 P) O, V) k
end 0 \" x+ ]$ G. q; [8 q5 O, D. cupdate tmp set name=@root+@name where id=1; b. j$ n5 c$ _% M+ X, J8 D3 ?! p
select name from tmp where id=14 G5 H- P! [1 v* L
% u+ l/ G( `2 K* I: t查找目录的语句/ z( t5 O+ w6 }- m; g. y
* J8 f6 K5 X5 ]. L8 z8 l9 \7 ^ B. _ b, K+ |( Z7 B8 k
CODE: 3 q3 A, d" `- j% [2 x! n% W7 D3 N" e: n2 x, y, x1 i6 w
9 X( w/ k, k U7 J$ i R5 h
drop table tmp;4 q- i- W6 Z. W7 f. n5 i, M
create table tmp/ `/ ]2 ?1 ]. N3 _# ?
( 0 [+ l4 A2 i; L5 l5 Y- j[id] [int] IDENTITY (1,1) NOT NULL,0 j; O' y; S) |% b; z; e3 A
[name] [nvarchar] (300) NOT NULL, % ]3 X) f2 l% Z* J# d. h9 y1 h[depth] [int] NOT NULL j3 k/ x. J: ~ I) T); 5 \$ q1 }+ Y# b- `* l" P ! s( {2 n; m7 N/ Y/ N' G4 jdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)5 s* E5 L! H9 d; O2 ?" P1 l
set @root='f:\usr\' -- Start root/ d& J" ~0 o" l5 ], \1 M. \8 C) p
set @name='donggeer' -- directory to find 3 U0 @* l+ b; N9 F# m% i% uinsert into tmp exec master..xp_dirtree @root,0,0: z. j' @8 Q7 W0 Q- t2 y1 K0 g
set @id=(select top 1 id from tmp where name=@name) j/ G+ {5 K5 \& A
set @depth=(select top 1 depth from tmp where name=@name) ' n/ n: w, D! \, C
while @depth<>1 ( ^7 _# D9 P- p6 L" P" jbegin 1 M$ E5 R% k* X# f% u0 X3 O- I* ^2 N
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)6 Q! V; F0 O" g2 @( s% R
set @depth=(select depth from tmp where id=@id) ! T7 a) r- D+ z; [: D
set @name=(select name from tmp where id=@id)+'\'+@name ! G; N4 H v8 |+ A. F# pend update tmp set name=@root+@name where id=1 3 \' D( `) q' ~# o$ N: @+ c8 k9 Nselect name from tmp where id=1