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

admin

查找文件的语句

CODE:

+ T! [  [$ p: S: \! v( \6 V9 Hdrop table tmp;
. t% {. i) {2 n! t/ O7 ?create table tmp
" N7 ?  G0 b, U+ ~(
[id] [int] IDENTITY (1,1) NOT NULL,
! q& x$ q( I. d/ W[name] [nvarchar] (300) NOT NULL,
- I8 S6 b" B3 u7 Y) z" A2 @+ ^, k[depth] [int] NOT NULL,
2 G0 ]4 Y7 f4 j4 t0 A! N- N[isfile] [nvarchar] (50) NULL
);
$ {( U' S' Q7 v7 I9 Z% ^
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--
3 W. p% ]7 V3 R; J0 Q% F' Dset @id=(select top 1 id from tmp where isfile=1 and name=@name)
' y3 k  ?: o6 }; B) mset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
begin
" i: ^; X  u6 h3 I6 \5 ]set @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)
$ Z' h+ M, n3 o" A$ Uset @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
; j2 v$ ~. c3 M
查找目录的语句

5 |. |, a2 q# W1 c7 `6 }( z
9 k) Q+ C( p  s. v: fCODE:

: r  H6 ^( H" N/ i) g( |
drop table tmp;
6 `' y  F% K) H# Fcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
7 z4 t1 z; q  }! p4 b9 E/ S);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
, G0 P- x+ v6 C. R: Mset @root='f:\usr\' -- Start root
6 x* z; i1 F* ?) O0 I. ?. u2 [9 T0 hset @name='donggeer' -- directory to find
2 b4 k- m+ d' ^8 l% h+ ginsert into tmp exec master..xp_dirtree @root,0,0
# J/ N" `# h' W$ iset @id=(select top 1 id from tmp where name=@name)
5 `' \& A; L1 O; p* {set @depth=(select top 1 depth from tmp where name=@name)
0 v& L: H& K  e" b7 bwhile @depth<>1
* y# ]5 ~5 h  \; U) `, Z; F' ?+ Q6 E2 jbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
, Q  [. f9 B* h' U: P' I. c. O5 rset @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