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

admin

查找文件的语句

- g" z5 G/ ^8 k; WCODE:
7 ?( [; X) D# }2 |  x
drop table tmp;
, M8 U; m! r0 p; Lcreate table tmp
! C: V3 @/ U7 H/ e(
[id] [int] IDENTITY (1,1) NOT NULL,
% `# v9 p0 A* R7 S[name] [nvarchar] (300) NOT NULL,
! v9 [* J$ _' D" E/ o[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
' g0 f! `3 R* n, g3 uset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
1 v# e: l2 Q/ w+ Rset @id=(select top 1 id from tmp where isfile=1 and name=@name)
6 {8 [- H! p% W6 Cset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
0 l- O% c" o: U$ ^( Z- cwhile @depth<>1
' H: Q( X3 D) V1 j/ e% pbegin
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)
# X0 l2 i, O2 s+ oset @name=(select name from tmp where id=@id)+'\'+@name
2 [  _# s  e$ e7 E, g7 @- vend
update tmp set name=@root+@name where id=1
0 B" L0 l; @; A" k3 s* Qselect name from tmp where id=1

$ k( Y6 p6 t6 I! N2 k8 J1 |# m# k4 L查找目录的语句


CODE:

+ A- L5 s* j  ~6 w
& z0 A: p$ z% \- O6 r5 x; Xdrop table tmp;
5 s8 f2 Y  i. ^" j" Pcreate table tmp
, I1 T( q, h- s/ F(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
8 @  s  l! x9 x$ ^);
/ p2 C2 S1 r8 K6 U& u
7 b. x  @0 Y$ r- p7 ^; b. mdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
9 [7 c  g! i& Fset @root='f:\usr\' -- Start root
& h# |. o0 Z6 hset @name='donggeer' -- directory to find
+ x8 v/ a# ~0 D: U9 U1 Y, Rinsert 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)
8 ^: _4 \2 S# o; Fwhile @depth<>1
. m% \, A) e  abegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
' h) U: R! a/ \' ~: A+ u7 bset @depth=(select depth from tmp where id=@id)
: q1 n& q. u7 H( Lset @name=(select name from tmp where id=@id)+'\'+@name
$ I& ^- C* O& hend update tmp set name=@root+@name where id=1
select name from tmp where id=1