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

admin

查找文件的语句
- A4 c: P6 b9 v1 I
2 Y  ?4 H. w  M/ c  LCODE:

drop table tmp;
: N+ w. R& N+ ^* W! r! H& L; ], vcreate table tmp
( V  U& e( m4 I- [+ w: b(
[id] [int] IDENTITY (1,1) NOT NULL,
9 v  M! k- {7 I1 k* G[name] [nvarchar] (300) NOT NULL,
. Y2 T' L# W9 F& H- [[depth] [int] NOT NULL,
$ u" c0 t* {# K# Z. h[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
5 ?  Q7 p: j5 {( H' X  n# A+ Rset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
, K: p" q2 \+ Z3 l6 u  N2 Y" nset @id=(select top 1 id from tmp where isfile=1 and name=@name)
8 G6 z: ^+ r* n! ]set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
- ~$ y( P1 a. @$ y' t" Z6 S  W# @while @depth<>1
& X0 b. z, C1 |* W3 J0 e/ abegin
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)
set @name=(select name from tmp where id=@id)+'\'+@name
' d  V* Y% h6 @, Uend
update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
, s( [) y  Y) Z2 @6 Y
( B! i6 V' |" R; Y" b
- ^3 }- u4 S# dCODE:

' _( s" o. M& J3 Z2 s' b0 \. j6 x
drop table tmp;
create table tmp
" V; S- K3 N) d: h/ X0 z0 Y8 h(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
/ [% }3 ~& u% v. w9 b- f& \[depth] [int] NOT NULL
+ N; L' I5 _6 i0 ?( J- f);

7 ?/ c8 W+ f# s( kdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
/ s; M. I0 J2 L! j# Rset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert 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)
! H3 `% u0 s1 n4 zwhile @depth<>1
, g1 w1 q9 N! }begin
9 o4 n7 q# Y) Bset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
4 R8 M  e% q8 @4 pset @depth=(select depth from tmp where id=@id)
set @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