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

admin

查找文件的语句
4 k8 T5 q- ~' o, R3 I  ?2 v
7 L& S: X2 m1 Z, d3 PCODE:

! E3 q! ^( @- L7 adrop table tmp;
% W- c) d2 \$ d: g3 icreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
( l2 o0 a& {$ B1 V" c[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

5 S0 C4 y6 o: v1 D! c" {declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
' D2 F1 u5 i, c2 |% e9 Kset @root='f:\usr\' -- Start root
* ~0 e7 V" V+ W+ J4 kset @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)
7 c- P% E! T* I% [' @% B# A' zset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
) o- i* m" t% F8 H' u. Mbegin
8 I* \$ c5 w5 U" a! hset @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
* z! O. ^4 q8 i$ M8 j8 Y; U0 mend
' Q8 Z  V3 O  a1 ^+ k4 yupdate tmp set name=@root+@name where id=1
7 {1 \  G/ T; {( t' sselect name from tmp where id=1

, q) e3 T! i0 V# R1 b, r* O查找目录的语句
- H0 d1 k5 y/ p) L8 m
3 {( \5 |( C) |6 H0 P; Z% u$ [
CODE:

; p2 z) C/ @/ P5 T" z3 b; S2 E  j
' b. f6 M5 N2 X* h; pdrop table tmp;
create table tmp
- k! P+ f4 d8 n$ t(
: Y% U- g4 K7 W2 R+ s; o7 H/ r[id] [int] IDENTITY (1,1) NOT NULL,
6 W) G, H$ n1 [% q[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
# W: \6 w! A4 m: D/ g);
6 ~2 a8 O# m0 L, x
, _. Y2 w* n- o+ ~  B- xdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
& O9 }& q; H# ]1 Q) ?insert into tmp exec master..xp_dirtree @root,0,0
8 l7 Q7 Q, L/ M  Tset @id=(select top 1 id from tmp where name=@name)
8 R0 U9 D5 ^9 x" Iset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
1 i% O% D/ a! i, v4 ^' |  mbegin
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)
( u' a. y3 x2 y+ l" X( Zset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
9 O  A& X3 B) Iselect name from tmp where id=1