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

admin

查找文件的语句
  |% _( b: b7 O( e3 P1 _* w! c
CODE:

drop table tmp;
2 P5 @8 ^5 _9 B/ ]. Fcreate table tmp
2 ^* q; p' }8 i2 q5 |* W) Q(
, y& ]; o/ Q+ T$ {" B' b, X[id] [int] IDENTITY (1,1) NOT NULL,
5 |- k0 k) h- j: M5 O( J4 s" S& }[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
1 o8 f9 c# E( b) n0 k. E* G1 n+ X: Cset @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)
+ q; i' y, Z1 X4 P) sset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
7 [0 ]) V/ D7 u& B: B- cbegin
6 m/ o# m- F% G" a( \# P7 @0 Fset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
# p) Y' m+ q; r- bset @depth=(select depth from tmp where id=@id)
9 f! a& c, i& U, ~6 p: sset @name=(select name from tmp where id=@id)+'\'+@name
6 U3 E! [2 x" ]; W0 s8 g; n# Nend
; b) x- t) _" X) hupdate tmp set name=@root+@name where id=1
# `8 j+ H2 @/ X. O& r" rselect name from tmp where id=1
, c) U  b  e" |
查找目录的语句
1 r" s' b, y0 J: @! M: s; X

) x' I5 m" ]" L. x! ?: Q0 TCODE:

( E8 g. W2 g9 v  P6 R8 T$ O" R
. w' t* t$ X& o: S( mdrop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
( I1 G: j8 e6 p5 m" {
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
+ x7 l# i! Y, J3 Jset @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)
while @depth<>1
( R6 L- E5 ]5 o( m  T" bbegin
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)
6 L* J1 ~: x/ G# e# bset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
, H( i/ j# u+ z  P/ lselect name from tmp where id=1