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

admin

查找文件的语句
0 B! q: v' \3 e8 P5 E6 J+ k0 z7 y
CODE:
/ w' ~" ]6 O$ f3 _) K! J
drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
3 Q: G9 ?0 ?* S+ y. O7 p, D[depth] [int] NOT NULL,
9 F* U! [( @( }; ~% x9 `3 D4 b[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
, t6 e# ]! Y' S: m) i7 }: s- Aset @name='cmd.exe'   -- Find file
% x' C* _% Z- K. y# uinsert into tmp exec master..xp_dirtree @root,0,1--
" p+ e$ U+ r! g) qset @id=(select top 1 id from tmp where isfile=1 and name=@name)
3 `: x7 @, ^$ x, n8 H( Lset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
" D6 |0 `+ W6 }2 M8 ~3 ybegin
1 m! I. J% m; Qset @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
end
update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句


& @( @  |4 l1 bCODE:
( C- H/ ]! j% D0 t+ K' F6 k% Q! h

6 f) ]3 S( ^' V/ A, U) _' @drop table tmp;
  L8 F3 v6 w) y% [create table tmp
(
8 s, ^, T+ E( N; ?- Z% c[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
! }- ?0 L( ?7 L& A);
; [" s% i) k! A! n! F9 ?* P
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
, j, ~, T+ ?4 ?0 ^) Fset @root='f:\usr\' -- Start root
( K- i0 [( }" a/ Mset @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
0 |# _( I6 F# e& z  Yset @depth=(select top 1 depth from tmp where name=@name)
. E7 _4 x8 ?2 r" {4 P* ^% jwhile @depth<>1
7 P6 m1 y2 }( `) qbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
. T, o: `/ m' [3 c. R, rset @depth=(select depth from tmp where id=@id)
, u! q+ L' g7 V  X1 _; f; Iset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
$ o! R  p; |; |& lselect name from tmp where id=1