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

admin

查找文件的语句
& f8 H" a0 k, _/ ^3 f# B
) ]4 H# ~+ z; r- {4 eCODE:

drop table tmp;
. J. w) k% X+ ]# fcreate table tmp
( b7 J% y- f) Q8 @(
[id] [int] IDENTITY (1,1) NOT NULL,
2 g# h1 H6 }' x3 ]0 Z+ i" ?- i[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
: l6 M7 f3 `4 P) J[isfile] [nvarchar] (50) NULL
3 s3 @- |' l4 P: C# K- p);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
  C6 M, X5 \  {3 ^1 E- dset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
6 Y3 m% S9 O$ I$ ^( Y' L2 Uset @id=(select top 1 id from tmp where isfile=1 and name=@name)
8 }5 N% C. T2 a0 ], E1 bset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
; j% a/ H( \3 Z, X, a6 v" X7 hwhile @depth<>1
' m+ [7 a1 }8 `6 s9 Y8 u% _begin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
1 t0 p  h$ h# F( o* T4 vset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
) \9 M3 w) C" }4 K" pend
4 ^( t3 F: Y7 A& k2 pupdate tmp set name=@root+@name where id=1
select name from tmp where id=1
0 {9 r" R) S; k6 q; W7 a
查找目录的语句


CODE:


drop table tmp;
create table tmp
1 K$ i( v) C8 m; ?! D/ K+ y(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
$ y% X) D& p" S2 h
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
: U; _) s# q$ f* X0 hinsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
* W  u) P; e5 J! x6 b* u  Q" vbegin
, K3 ^. k: I+ w( p2 q$ W! aset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
6 n1 f0 y9 m/ \" {* v/ J5 i  _2 ^set @depth=(select depth from tmp where id=@id)
3 O% s6 D& X& m/ |! ^set @name=(select name from tmp where id=@id)+'\'+@name
( L7 m% I: d' @8 B( D9 O  }end update tmp set name=@root+@name where id=1
select name from tmp where id=1