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

admin

查找文件的语句
$ {& K& B4 ~; Y3 D- I2 {
CODE:
$ y% F1 B1 m$ t
1 v# G2 z% S0 ldrop table tmp;
create table tmp
. ]' c2 m7 Y) Y2 E! I(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
8 l8 B6 A8 V' @3 a" i5 l8 K[depth] [int] NOT NULL,
- Z" w' o* Y7 c, j/ W8 c3 F$ S[isfile] [nvarchar] (50) NULL
% Y) t4 ~  U' w% m);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
) e1 H( f8 Y' k' sset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
6 J- i, p5 x/ {+ l! dinsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
! D* |% T7 d1 Y4 [6 U! l, p# V% E" yset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
begin
! a( U0 q+ t3 u' K& q; a/ sset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
8 N6 b8 ~8 R& H! S3 u: ?; H  jset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
end
/ `  W; q* n5 k7 e% p3 _update tmp set name=@root+@name where id=1
* Z* p4 g  |3 W7 cselect name from tmp where id=1

) x0 X8 Y& a6 [! Q* b查找目录的语句
8 u' _7 O! H; g5 u$ P

; L$ T# |1 ?/ q& c& {5 JCODE:


drop table tmp;
create table tmp
4 ~1 O9 p/ g. l; J& y6 f7 k( w" n(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
( M  ^" y1 N3 {[depth] [int] NOT NULL
, B9 Z5 o5 ^8 J3 c( b3 `+ `);
0 w* t7 T- G. ~8 H) ^
! N5 x3 I/ @8 v6 L7 f  \& n- Qdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
2 W+ y/ W* {, cset @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)
, y- R1 f; k, \! `$ p. v' ~set @depth=(select top 1 depth from tmp where name=@name)
8 r  s7 c7 r, w0 t. m& Xwhile @depth<>1
  \; H+ X  V2 cbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
" \! X: @9 V* M2 W5 J- gset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
7 S( d& B' m8 e# u$ g3 N1 V8 Q; }end update tmp set name=@root+@name where id=1
. E6 w5 k* h. L1 P5 s8 Mselect name from tmp where id=1