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

admin

查找文件的语句
! a9 |# w- a" L7 Q$ ]" v
, M1 e$ O) A3 \) SCODE:

6 S8 X# @+ S, Q; i) R: k3 Idrop table tmp;
% {/ B( K" P) ^4 V( E  _; xcreate table tmp
% f& Z9 r( r! m: Y(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
- _& M4 P; K1 D; j[depth] [int] NOT NULL,
6 Y; v- z! U% F0 [+ G$ c[isfile] [nvarchar] (50) NULL
);

: s3 _+ v! C6 l( b+ G9 fdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
7 T! c) w6 c/ f7 Q' J( fset @name='cmd.exe'   -- Find file
6 J  I% J& u: i3 k' Jinsert into tmp exec master..xp_dirtree @root,0,1--
; s/ y( Y* ]2 M& Z1 y: ~1 N4 Kset @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
+ K* W7 q" `+ }' c2 D7 u. y* zbegin
$ J% }8 m5 Q$ k% K9 N$ S0 vset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
5 J; W4 G  a! J- l/ |( ]6 A+ \3 Yset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
9 i6 `, }4 \% v+ x  Dend
update tmp set name=@root+@name where id=1
select name from tmp where id=1
3 a$ b! ?3 W' c1 ~/ U, J: r
查找目录的语句
+ t) {& p9 e1 j, j

( U% ]6 l: k$ _% NCODE:
0 d3 E. H+ ]4 U* o  y

drop table tmp;
create table tmp
. o2 }2 \" I% [! G" ^# |' |(
, ?5 Q+ b' s( f: {: g+ |8 B$ [# t[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
, A5 N$ v' S) Q6 Z);
) ?- K0 S( ?2 `6 Q; D. e% f( T8 m
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
& n# p6 z( }1 y$ Wset @root='f:\usr\' -- Start root
7 l  ?2 v) ^, x' X( k- P: iset @name='donggeer' -- directory to find
" V. k& r* g$ Y8 Zinsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
7 G: K+ R% u4 N* K: \) z9 [set @depth=(select top 1 depth from tmp where name=@name)
5 H3 X! K1 r7 rwhile @depth<>1
% }2 E$ W# r6 ]. l/ E$ }begin
. c3 ]2 {- D1 K* W5 Pset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
7 L- ~: ^# t5 g. `set @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