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

admin

查找文件的语句
% X3 k% J6 ?! w, R  ]
CODE:
* j8 a  s3 b% q* i7 a1 ?* i$ ?
drop table tmp;
/ _' j6 D  ^$ w7 u6 Rcreate table tmp
+ [, n$ [+ s5 ^( H2 k(
6 Z5 {" U- ^" R; S/ d8 |[id] [int] IDENTITY (1,1) NOT NULL,
6 {* H, h) @9 U[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
) I2 f7 x2 a+ R' Bset @name='cmd.exe'   -- Find file
  Q" a! H4 {7 A: K% ainsert into tmp exec master..xp_dirtree @root,0,1--
6 o5 q' P+ S' t0 y0 K5 Rset @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)
' r9 Z0 A5 u* F- W9 }while @depth<>1
begin
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)
5 t3 ?0 K0 r. oset @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
$ i8 |7 z8 _8 O0 |" l0 q8 N. V& Wselect name from tmp where id=1
0 m4 i# I6 _  s
5 m3 M5 f1 S) M/ n# _查找目录的语句

4 u( T& b. }* m9 l, J1 p! [
/ |; u; ?7 M1 \. Q, ]CODE:


drop table tmp;
create table tmp
; i: m5 I. w& u$ ~8 d0 ]- J- k8 X- y(
1 H3 Y0 r' ^' t. _6 Q' o[id] [int] IDENTITY (1,1) NOT NULL,
7 [2 [$ q: ?- `+ N5 Q[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
/ B: I# m8 o3 J' M
" D1 \/ K) o" H- w) ldeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
9 z2 i+ v  k5 Qset @root='f:\usr\' -- Start root
set @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)
; a( B' ^6 U$ C0 u1 o7 ewhile @depth<>1
( G; O0 A3 |0 i) {! }4 V2 Q, L+ fbegin
set @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)
set @name=(select name from tmp where id=@id)+'\'+@name
" U& s& j" T4 c/ ?end update tmp set name=@root+@name where id=1
. z5 t6 P  o: J, G* ~5 {select name from tmp where id=1