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

admin

查找文件的语句
4 l+ X+ v" i5 P6 }. Y/ [" J
4 Z4 j* i0 |+ S! D; o  }" R! c* bCODE:

drop table tmp;
6 O( m; z1 o! [# @create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
9 Y" y7 Z% Z, s" K2 m3 ?9 G[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
" `$ G4 t* |. Q: Vset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
& d9 i4 d+ q# L% F  X3 b( ]/ x: Nset @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)
- O% u5 D# T+ |! c6 rwhile @depth<>1
: j% d* L! ^/ Y/ K' m" |begin
5 ?% s6 w/ h" G8 L: Tset @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
3 @. P/ C0 g) \6 Hend
7 s! Y) X+ |8 G" T& `2 o0 pupdate tmp set name=@root+@name where id=1
% R+ ~$ a" ^+ t+ [- tselect name from tmp where id=1
5 U3 _% O* r4 ]' ~
7 x5 |8 h! j% v$ o5 O$ s8 _查找目录的语句
1 b! w0 ]4 S$ w  v9 `% S
2 _6 F* S7 Q- [7 ^
0 E% v# s  q; @. o. y- B6 RCODE:

- E7 C7 ~7 D; S5 @+ g
$ h( H* e8 t) W+ |drop table tmp;
! A0 e0 M& V$ w# K( ]! a& v# G1 Ncreate table tmp
$ B' S3 v7 o* ](
' @6 O) {) ?* \; s[id] [int] IDENTITY (1,1) NOT NULL,
7 k4 H3 j8 }( ]. d4 Y[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
0 q  F8 R2 u0 \. h2 Z
& B* q  ?7 j& Q: T2 B; U  ^declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
4 p* \6 }1 G% J: w; n" h! e7 Minsert 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
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
. N% _6 l9 r6 V' F: F8 vset @depth=(select depth from tmp where id=@id)
4 a, p8 v, @' f; {set @name=(select name from tmp where id=@id)+'\'+@name
; A" j, Z: S' k6 w0 iend update tmp set name=@root+@name where id=1
& ?2 O* r8 P" @9 K% U3 xselect name from tmp where id=1