快速度查找文件和目录的SQL语句

admin

查找文件的语句
# g, e, ?- X& o7 P' [
CODE:
  X( t4 g: I2 V0 q! P: {4 T
. S( J$ V* b: w8 N# Edrop table tmp;
create table tmp
, [9 V5 `! T+ G3 v3 E3 Q(
[id] [int] IDENTITY (1,1) NOT NULL,
  X5 ]" U! O! H5 X" }" k[name] [nvarchar] (300) NOT NULL,
( ~0 L  f6 s( {[depth] [int] NOT NULL,
* V( D% y+ [9 P' S& j* J9 C) F[isfile] [nvarchar] (50) NULL
);
# B4 y! f0 u) E" P1 e
  {7 {5 Y; z7 e( {9 x7 Y" Bdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
% I: w) [* {9 qset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
7 D( l, G1 z4 x9 j4 @% ^set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
begin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
set @depth=(select depth from tmp where id=@id)
5 S. F. B$ X: d, l2 }4 B: oset @name=(select name from tmp where id=@id)+'\'+@name
2 X0 @* y0 z# l% B, Xend
5 a3 [" r/ L' G" C+ h9 K. oupdate tmp set name=@root+@name where id=1
4 W8 E4 g6 ]3 [7 ]+ l( @: M# Rselect name from tmp where id=1

查找目录的语句

. c8 k0 E1 w2 Y$ {  R" Q# c
; o& q  m2 h( L  n' c5 sCODE:

3 u8 O2 h* \, j  b
- k& x4 _0 h; f  w" vdrop table tmp;
2 B* \6 [3 `9 p, Hcreate table tmp
3 V4 _) q7 m+ ^- w8 m(
[id] [int] IDENTITY (1,1) NOT NULL,
6 ]+ o$ E: @' A4 Z" D[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
, F* K. z# ?2 K+ y
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
: [! A8 F: v' u& j) N, o9 i8 Dset @root='f:\usr\' -- Start root
/ g' s$ U1 y) V& i" Nset @name='donggeer' -- directory to find
. w2 f, m) A$ X. T! N7 `% ninsert into tmp exec master..xp_dirtree @root,0,0
6 @+ \. J2 J3 \4 E- y9 _0 dset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
: z1 q" P5 w6 x/ uwhile @depth<>1
$ D: U) g% _3 h! }) `2 q# |6 fbegin
% z  W7 \! t7 o2 _9 xset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
6 N! P4 D# P4 |( c: l9 |6 W; n' f3 Fset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
7 B* }" g5 v; v, {: O5 E+ D3 y2 Fend update tmp set name=@root+@name where id=1
select name from tmp where id=1