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

admin

查找文件的语句
3 e: j1 ^& g( ^) u) u
CODE:
! z6 R7 \9 P! {4 W; r' i5 H$ @9 m
  A/ p" D  d! K+ H/ d! Wdrop table tmp;
! X3 H' t. v6 gcreate table tmp
4 p6 C- z4 T# q# A- F(
5 b, F: s) A- Z) r0 i  P[id] [int] IDENTITY (1,1) NOT NULL,
% h7 F" F: e) X* k% |[name] [nvarchar] (300) NOT NULL,
, H, ?: }3 @, _# ^# S# B[depth] [int] NOT NULL,
) w1 q" U/ E/ @1 z7 [  E[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
. o3 C  P$ f  ?/ Uset @root='f:\usr\' -- Start root
6 i2 }( W& V8 `3 T, M8 Zset @name='cmd.exe'   -- Find file
- e& V& I( R+ c9 i! Qinsert 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
9 w- h, s) F9 Nbegin
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)
% J8 e7 ~# j5 _9 D2 K# }set @name=(select name from tmp where id=@id)+'\'+@name
& m. F# w! U' L- L' v1 pend
update tmp set name=@root+@name where id=1
select name from tmp where id=1
9 m3 |: ]1 y/ ~8 a
' [7 g  W* `1 {查找目录的语句
. [9 k" B. T; k% f
8 B$ P6 s+ z% i8 v
CODE:

7 m, O9 ~4 a& b+ r
2 K$ Y; e5 O; k: V7 p6 c1 }1 Hdrop table tmp;
7 l' I6 Z$ b! {, ~0 d' ccreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
$ n( p3 _. v6 S5 B1 O[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

0 l8 g9 c9 U( D4 ^( M+ kdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
& P+ x7 R# K, H7 Z$ e' j2 Hset @root='f:\usr\' -- Start root
5 H. w( O6 E* l& Bset @name='donggeer' -- directory to find
- g& t3 W4 V& Y* n3 c# H; Oinsert into tmp exec master..xp_dirtree @root,0,0
2 M4 l# t- H: ^# [+ ^) M9 eset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
, E2 l$ G2 a- Y0 E! _( [& j( Jwhile @depth<>1
begin
7 B1 `/ C9 w0 R# J& Tset @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)
0 S4 n6 S/ W) P' @0 Q8 ~1 uset @name=(select name from tmp where id=@id)+'\'+@name
& h8 n5 f- w- z3 x8 G; Gend update tmp set name=@root+@name where id=1
select name from tmp where id=1