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

admin

查找文件的语句
0 J( J* M& d0 Z* ~1 S3 D
CODE:
7 E- g/ \% D. Z0 a6 y. A0 G/ Y
drop table tmp;
create table tmp
: w% W3 G# ~+ I) i0 R, u& ~9 [(
3 }5 X# Q0 e2 h) F6 J+ h& m7 {[id] [int] IDENTITY (1,1) NOT NULL,
0 `7 h; I% `: k6 g- w[name] [nvarchar] (300) NOT NULL,
* J7 v$ p( r2 Y* w7 q[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
9 v- d' E* i, ~( w9 `5 H
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
/ b* `" I. k  }3 S; r4 E# K+ }set @root='f:\usr\' -- Start root
; i. ]1 r2 x* r3 z- V5 {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)
6 d/ C" _- s. g( c2 {0 dset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
: D1 [2 W5 h" q) rbegin
/ t* G1 y. p% v$ \1 e3 f6 Vset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
/ ?  ^+ ?8 t! y: G5 ]" a9 Kset @depth=(select depth from tmp where id=@id)
4 V5 }+ y7 `* W* e$ K1 Z9 E3 c7 nset @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
& i0 k$ S" J( Cselect name from tmp where id=1

+ [6 E, F" }) u! x# Q# V* u6 F+ ^查找目录的语句
- b* w$ H( B( Z& [2 v

CODE:
5 _( Z' ?) A# m" v8 m; L* Q

7 f+ J) i1 X+ D& Bdrop table tmp;
: \! \. t5 B7 W9 B2 a5 vcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
* ]- [5 d) V8 Y% R4 b! R[name] [nvarchar] (300) NOT NULL,
4 y( V# l; @2 H1 }6 j/ D[depth] [int] NOT NULL
);

1 {) E+ h1 W% S; }" T8 adeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
4 Y  M) r8 v4 p8 w2 N4 aset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
5 ?$ E( @( P! m/ Cinsert 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)
8 {2 T) ~/ A; K" t' ]& z5 ?& e. Kwhile @depth<>1
begin
. a) m% f$ O# x9 z; o3 f0 i4 L. [set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
' u: o$ }7 `2 Hset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
/ ~5 o- o# ~5 X' oend update tmp set name=@root+@name where id=1
select name from tmp where id=1