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

admin

查找文件的语句
1 q; H  Y/ _2 u
CODE:
+ z8 [) w$ x+ S) f( ^+ }7 l9 z
drop table tmp;
- i" L8 I" p5 N" L1 Gcreate table tmp
( \) j, s+ q* s- D; h(
[id] [int] IDENTITY (1,1) NOT NULL,
$ U' b' Z6 I" C7 y& k. D[name] [nvarchar] (300) NOT NULL,
3 o7 q: X: N  Z# D! \[depth] [int] NOT NULL,
5 b5 g/ x9 I) i. X4 b# ^[isfile] [nvarchar] (50) NULL
7 p& l3 }- m+ r" z);

9 W; M4 L& t6 ?9 E' cdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
- ?( i, r4 d4 Ninsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
8 T) d9 _4 z  K7 d8 ^+ v& L. oset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
& \. W/ z5 N/ w: l4 \4 xbegin
. a9 A& ^9 @' X6 b2 F/ s; o5 nset @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
% P; K6 t* b* s; r( ?% gend
2 y' @. k' c7 S" S* s4 Qupdate tmp set name=@root+@name where id=1
select name from tmp where id=1
; A/ A* N* J% j& y2 Q, Q
- O& m9 D3 \% C- f查找目录的语句

1 t! H7 g" }; k1 V
; m, U: X, j; u. D0 h* hCODE:


& h) x+ v4 T$ \' s7 @drop table tmp;
create table tmp
9 w8 X3 V0 u7 P6 z$ K(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
+ k3 \9 w1 y% n( Z* ^8 \
3 T9 _5 C+ }4 sdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
3 m8 X) L' D5 b4 H3 a% ?2 |8 fset @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)
set @depth=(select top 1 depth from tmp where name=@name)
: y. u( v0 _! ^: K+ k. Q$ |while @depth<>1
: T4 K. _. G4 X; n5 u8 sbegin
) ]8 z) J3 e3 k7 S! l1 J+ Uset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
set @depth=(select depth from tmp where id=@id)
: I4 R. _* Q+ \$ O0 Zset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
! n# E8 S  s  Y8 I; G! Pselect name from tmp where id=1