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

admin

查找文件的语句
( J( W( D# Z0 ?. s8 `0 ?* H
5 r2 e- q( Y9 GCODE:
7 b; |0 g, H+ K$ p" K( S" j2 o, [
  ?% e( k2 T( e& Y, j) |( Z2 @3 Odrop table tmp;
create table tmp
5 g- a/ D  A9 d( A: a(
1 B8 q/ ~9 `4 a, b7 i! Q[id] [int] IDENTITY (1,1) NOT NULL,
" C+ \  W% k. J$ `" j  S5 s[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
9 y8 ~3 u3 o7 a, ~9 T[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @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)
set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
6 F$ I, d5 f4 T3 {' ?begin
# t! F6 e6 w, U; }, `& mset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
; v' H  h; W: m. Bset @depth=(select depth from tmp where id=@id)
  q+ M5 x  m# a4 |/ r- cset @name=(select name from tmp where id=@id)+'\'+@name
. I! r; S0 P. J. I6 \end
update tmp set name=@root+@name where id=1
- a3 P9 o+ h0 p8 o9 }select name from tmp where id=1
" s8 C# s5 q% H. O4 d
查找目录的语句

4 [2 P8 V) l2 B. y& N1 I0 D1 @
* g; |6 V0 O1 _' x- }CODE:
" l0 K5 r' q' j' J1 J4 a
/ b. y! H6 D% @3 j
& O$ c8 t$ ^: B  y2 y  U6 |drop table tmp;
) X% y1 Z8 V; R: [5 ?1 Lcreate table tmp
% t& G- }8 V: y' `2 D) j# ](
[id] [int] IDENTITY (1,1) NOT NULL,
( E6 Y4 b0 Q1 j( k" @/ _$ C[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
$ t5 }+ _& x- r: p' H: R);
  ]1 y. I0 {7 J/ ]9 x
declare @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
. G) E; X) w4 p2 _& t0 Aset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
+ O- z6 A! q( {5 Lwhile @depth<>1
begin
/ u# ~  K4 y1 k2 B! A7 tset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
  i! p" U! J! R2 Q2 }1 X7 eset @depth=(select depth from tmp where id=@id)
- |# ~! \; o& c) K4 `- lset @name=(select name from tmp where id=@id)+'\'+@name
+ F8 {3 C4 B  Wend update tmp set name=@root+@name where id=1
select name from tmp where id=1