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

admin

查找文件的语句

CODE:
1 r* R# q: d, r! P5 s1 c8 P
+ k/ J( a7 G$ Idrop table tmp;
% M% T6 h8 h- r# i7 U8 Pcreate table tmp
3 n5 E: \- Y* g7 E6 g+ c+ h* z* s2 [(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
& Z$ ]4 n; H) _3 v5 C' c7 w[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
( e7 }6 d, {$ m6 X
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
0 b' f6 z, p0 P- a5 @set @root='f:\usr\' -- Start root
' C" D8 m) k% P: C4 tset @name='cmd.exe'   -- Find file
) P5 L7 h) l, F3 d/ Linsert 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)
while @depth<>1
' k- f5 k! T3 R" j4 a( O1 ?9 lbegin
# Y6 Z4 @* C8 ^( p) Qset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
- j8 z6 ~! v5 H) M+ B5 ]& V! F6 pset @depth=(select depth from tmp where id=@id)
4 R* }9 ]- N  _8 Sset @name=(select name from tmp where id=@id)+'\'+@name
9 C6 b( T( |6 y0 T+ V& Yend
. L3 U+ V/ y1 ?" wupdate tmp set name=@root+@name where id=1
select name from tmp where id=1
6 C4 ]$ J  r( v* @1 J7 T1 D
1 _/ j3 |) D9 S% `" \查找目录的语句


# A; Z4 w' m. q8 {CODE:
% t" _, ?4 D& q

! x: s5 S- G( Q1 G# a+ _drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
8 V# [) a' K' f; U[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
" Y- f3 S1 m# _) p  ]. [+ V
4 Y; B% D  C7 i. R4 g9 @, }" [7 {declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
, H& X& b* G& [7 s/ U7 Y+ qset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
1 w# D- x; a: rinsert into tmp exec master..xp_dirtree @root,0,0
' k* k0 O9 X8 v+ N2 c0 z' q5 b3 k. W; cset @id=(select top 1 id from tmp where name=@name)
; H9 A, D! d6 w8 f) o* d5 B7 iset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
- S- O' H" m# S8 cbegin
, ^; A* y8 [- a( Q9 zset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
" Z, t) k+ i- f. `! `) k% Oset @depth=(select depth from tmp where id=@id)
1 l. U6 u: a  F! o- S( ?set @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
6 v  h- P. f; }" J3 k6 x* y2 P" bselect name from tmp where id=1