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

admin

查找文件的语句

" p! h0 N1 ?: X1 _/ `- n1 O( f! \CODE:
8 a  Z/ w7 H9 x7 X
drop table tmp;
0 l# a. H3 b8 ^; {create table tmp
/ H3 t" w. N* b(
) |  Q* C2 @7 ~[id] [int] IDENTITY (1,1) NOT NULL,
9 z% T* ]0 F6 @) i# w* F[name] [nvarchar] (300) NOT NULL,
: L2 m3 d. H. t  q% P[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
% V  ~( \  Z( |: m);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
- L6 v0 P! |- @5 e1 o1 m( N8 eset @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)
  ?6 l& o. y6 E, M7 xset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
. @% Y* X4 ]( M2 U6 Hwhile @depth<>1
0 g! T1 m- L: E# zbegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
3 z) p+ N1 r. r1 M9 v1 Fset @depth=(select depth from tmp where id=@id)
% {1 V3 v0 @  g4 x: g" Yset @name=(select name from tmp where id=@id)+'\'+@name
end
0 @; l% R6 O$ Lupdate tmp set name=@root+@name where id=1
- T+ l1 D: ~# p4 j% V; cselect name from tmp where id=1
8 Y" `; m6 }0 ~
5 m' T- F- ^  u查找目录的语句
$ q; j8 ?+ ~% e. a
6 c2 L' e% ]  }
8 J5 X- T; i( a) S' W4 s3 nCODE:
6 b( d4 [2 T" B- H' [$ v% U; e
5 h9 h/ r. l9 A9 F9 R) d. U9 u6 x
drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
# S3 l9 x( ^/ O- ~0 i% J; `4 X2 r[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
4 Q' P# F7 p! S( }0 r);

- D6 I0 M0 J1 G9 bdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
- O. \" [1 h  c2 {, tset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
: Q! f$ }, U" f  T( Q; V. A7 s4 ~set @id=(select top 1 id from tmp where name=@name)
0 j/ |; o7 i- oset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
7 |2 n- k1 C+ k* g* D: @begin
7 \8 O+ E! i6 S# Oset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
; q7 i$ J' U" U9 G+ g4 @set @depth=(select depth from tmp where id=@id)
2 z, x; s4 u" S8 Z* f6 pset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
select name from tmp where id=1