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

admin

查找文件的语句
1 `) U" P9 ~0 [  ]
  h0 l. L: Z1 z* d- d, eCODE:
) a6 y( [, `. @! p! Z. Q: M0 _
drop table tmp;
7 s3 Z; E8 V, Y  y% |+ n) zcreate table tmp
# t. z+ h. @. J/ m) d* X3 R3 N(
! d/ c0 f+ O- M- c! D+ p[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
- P2 Z# X# l6 ^6 a' C4 O3 g9 K. C
+ v8 r1 o' ]5 g7 w/ e# ^* x  W6 X8 V; ddeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
% E+ E6 f( Q; Oset @root='f:\usr\' -- Start root
7 h% t' j* T+ e$ pset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
4 e8 \  m* ?  z, |8 _set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
' e  V! E' @3 D+ j8 Xwhile @depth<>1
begin
7 ^1 D( J5 {8 A& k; qset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
% ~& q/ m) V3 w$ z; V2 ~" n* ]set @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
9 Q- w; \% U6 j: e' l% A: zend
update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句


2 D; d3 a, d8 g3 D) OCODE:
: a: t6 i9 ^, `4 I

; D4 c) V4 G! c: S% qdrop table tmp;
$ x% e% l3 [5 t1 E, H7 o# vcreate table tmp
2 V% s3 B/ k0 P" _# n(
( i/ E" \4 o% t& I6 U: ]2 ~[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
' k% X1 q! [$ I# x
3 y+ j! D5 J: ~- Gdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
# E0 a/ \9 ]% k. y. jset @name='donggeer' -- directory to find
5 m  @  J2 E; g9 T0 A, I% @, Oinsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
3 P& n$ ?; T, [& dset @depth=(select top 1 depth from tmp where name=@name)
$ n  u: L; F2 \1 ?9 Dwhile @depth<>1
) {0 P' {! B. u7 abegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
8 x& @5 e2 j3 ^set @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
" w9 q2 S  \4 q& B  _end update tmp set name=@root+@name where id=1
select name from tmp where id=1