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

admin

查找文件的语句
/ \+ R/ Q  E' x6 ]+ ~2 S# ^
% y2 `" S: u/ V& ]" y# y( v6 \$ nCODE:
1 k) `! M+ L  k7 k+ d
drop table tmp;
- ?( ~; G  q* C: y0 h  @- hcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
9 H0 a0 U, Z3 I[depth] [int] NOT NULL,
2 g3 v" u; u& A, q7 @[isfile] [nvarchar] (50) NULL
& k: b; t/ Z$ L);

5 W% O3 \% _( h3 U2 Wdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
. q' b7 u( |& {( X" Cset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
0 e+ L  k' v# d/ Y" ]; Pinsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
5 v8 M! r. v$ x/ Y0 G. z$ F  ?# eset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
% Z( x4 i6 Z3 L: K4 r9 m* Cwhile @depth<>1
3 V* }' [5 L. Q5 Y& ^* a1 Pbegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
3 f5 f5 E7 S( Kset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
end
2 @$ I# w: ]2 B( v; B( bupdate tmp set name=@root+@name where id=1
# J( @  b" s% C; p9 q7 jselect name from tmp where id=1

- X7 U/ x. g9 |* e# l查找目录的语句
" M) ~; t% f& h8 a5 @( l3 O" E
' s6 ^' U& `- z" i+ r: _9 E
CODE:
; x! R: z, h& X
7 [3 g$ _! V, w# U- U1 O
drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
& V: Y' `9 t7 O# n);
& {6 d! ~5 X& M) ]! w" D' F5 E
5 H: n) \, Y, a3 f9 Ldeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
+ c+ |/ {9 X( Yset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
( \: H5 }1 }0 N) R  Mwhile @depth<>1
: D. Z' X( c3 }- |+ _, Jbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
set @depth=(select depth from tmp where id=@id)
0 F) ?1 ]1 u7 Wset @name=(select name from tmp where id=@id)+'\'+@name
4 K3 C; S8 F% C  r" Eend update tmp set name=@root+@name where id=1
0 |' k3 ~0 `* v4 P  xselect name from tmp where id=1