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

admin

查找文件的语句

% I( j5 u# ^( s3 @2 q3 Q9 b- LCODE:

drop table tmp;
1 s9 K3 D; @" ?9 Ecreate table tmp
(
$ O/ ]# N7 F6 ~% P' H' H[id] [int] IDENTITY (1,1) NOT NULL,
8 a5 s+ E' I% V: N6 v[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
# [0 s; X+ r' Z[isfile] [nvarchar] (50) NULL
- J- r+ |2 e! i. f6 `$ N) j0 t3 l3 f( a% i);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
* G+ Q5 Q+ X0 L6 Zset @root='f:\usr\' -- Start root
* M8 l: W$ P/ I  {! xset @name='cmd.exe'   -- Find file
" _9 q* u, O+ jinsert into tmp exec master..xp_dirtree @root,0,1--
/ E7 z" ^9 o  y; e% w9 w: {) 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)
' s6 W9 U, Z: k* Z' Z8 Nwhile @depth<>1
: v, A3 h+ r: _begin
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)
) ?# }4 M$ |: |: ?5 e3 cset @name=(select name from tmp where id=@id)+'\'+@name
- g% v+ y! G/ r  e7 a7 ]end
$ z' Q; f$ G' F4 c. S3 ~update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
/ N! n  S: u, m

CODE:
/ N- K6 [( }1 {8 R7 R" W
# |4 o7 X5 j4 m% c( t; K
drop table tmp;
2 Q2 `0 Y' H5 Y6 ecreate table tmp
9 ]" p& y  L3 j7 P/ b(
0 _5 h4 \9 o5 r, D  s[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
4 S- g) ^( E3 O+ m; d[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
* w( O/ D; l% Iset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
. O# j& v  L* A: Cset @id=(select top 1 id from tmp where name=@name)
) a) u  B  a$ g8 D( b' h$ Tset @depth=(select top 1 depth from tmp where name=@name)
. I$ E9 L6 h( e: ]/ [4 V) @$ \while @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
" X4 u4 J; d/ Bset @depth=(select depth from tmp where id=@id)
. m5 N" T. ~& R" `/ Gset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
# P1 C0 o4 C! B$ Zselect name from tmp where id=1