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

admin

查找文件的语句

! L( \& s. K0 Q) L% J) ~. \9 KCODE:

drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
! I1 @! F1 z& U, Y. \$ `# a6 ~[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

6 l' T3 q) y( Z. E5 Y- hdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
: ]# I# k! e) _. U: n6 pset @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)
+ @# i& }' S3 j6 vwhile @depth<>1
begin
1 S* ^: w7 j: c# N" uset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
( l; D. O" N. tset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
end
+ ]" g0 x+ Y3 z! }) d9 O, N% O# Zupdate tmp set name=@root+@name where id=1
4 G: P( H/ X4 d6 e5 `select name from tmp where id=1
/ G) a; @4 T4 u) b0 Y8 A
! X. P/ y$ D5 }5 e查找目录的语句
) [. c( D/ a* ~  G( a

CODE:
% Z5 m" @& H/ i( Z& H6 v
% G: M+ B% J% b1 }2 a2 I
& I( L+ t! K. J/ t4 rdrop table tmp;
create table tmp
3 E1 x! S1 B9 ^' K(
3 ?, |) S# u/ Q) a; {9 |5 K[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
2 g3 O1 b7 l7 j% g[depth] [int] NOT NULL
: Q' U+ q9 C5 m; B);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
' |, @) Z4 L8 Kinsert 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
" [# i2 I4 @1 ?begin
7 Q' }+ D; m& L# Gset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
4 s8 `) m5 v- o9 g- H. g% q! Kset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
; s# o- W* o0 P3 S1 Fend update tmp set name=@root+@name where id=1
/ o1 Z8 P! w+ G) D" Z2 Zselect name from tmp where id=1