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

admin

查找文件的语句
: G! x1 h2 |: B: R- P: K. F
' y' u) ]( [; c% ]2 U" bCODE:

: A6 m9 q- k& Y' \9 c8 Z1 Y8 [drop table tmp;
create table tmp
: [3 Z6 t/ e5 I* O0 R8 y0 o(
# ?- |! m7 E4 m; x; _[id] [int] IDENTITY (1,1) NOT NULL,
/ a: ]  E  i" p* H5 L& K[name] [nvarchar] (300) NOT NULL,
& p5 c. ~! r6 |8 G% V& i7 y7 m[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
  f, k3 t$ R/ {6 n
# C4 J5 e2 B# R" ?: A2 Sdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
2 k! \4 \7 ^: k+ z* w, [0 a. ]set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
( Z1 R  @* a( _+ u& w) ]2 Lset @id=(select top 1 id from tmp where isfile=1 and name=@name)
set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
! z% L2 j: V( y3 R" `begin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
0 G! ~1 T$ [, ?9 @4 P6 X. Vset @depth=(select depth from tmp where id=@id)
. w5 {5 x: f& L, |! cset @name=(select name from tmp where id=@id)+'\'+@name
2 z" F) `) T+ t: l$ _end
$ f- X! r- D% Rupdate tmp set name=@root+@name where id=1
( b/ D$ h5 Q. N9 ]+ O0 b6 S* uselect name from tmp where id=1

, L4 L9 Q# X+ s/ }, U9 @3 a查找目录的语句


# q0 p3 i7 N6 ]4 M# E  K) Z/ F' OCODE:
1 H  m2 e% _1 c& o' V( g8 w
/ r& y1 }; t: F) Y+ l! x' G
drop table tmp;
create table tmp
+ J0 m* S1 b" [( B7 @(
/ D6 ?4 R$ K  G' L5 k/ W' ^: {[id] [int] IDENTITY (1,1) NOT NULL,
* g( q3 \$ n7 v/ |5 a[name] [nvarchar] (300) NOT NULL,
# N) D% t0 N+ z[depth] [int] NOT NULL
  F: T1 L( T: _& T/ U, U);
: Y  ~# c* X0 C  E% w8 Z
2 X) ~! j. X9 i% y' pdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
- T% k" {% ]% B( tset @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
2 O) t( s. d/ dset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
7 X  b  ^0 ]6 a: t- F1 S0 ?while @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
8 g1 G5 s+ V4 D" iset @depth=(select depth from tmp where id=@id)
+ r. f- k# f1 c( g! fset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
select name from tmp where id=1