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

admin

查找文件的语句
+ c  A* \8 G8 }. Z6 V2 j1 U
CODE:

drop table tmp;
create table tmp
' }+ ~9 F% M' A(
[id] [int] IDENTITY (1,1) NOT NULL,
6 z6 w  t+ ^6 f& T* o: N4 }[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
# }  H5 a! R" |' N8 x+ ^
. M  G, H# s# }& j! D; tdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
' w7 x0 S5 ^- b7 j  Z# |7 qset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
" R& C; S7 Y7 y& z" h4 vinsert 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
: x3 p8 }. e& g( Y& E# H0 p" abegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
( d% u1 B0 m/ R& F& A0 v1 M* Pset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句


5 d. O! x, e/ s1 T! ]CODE:


9 n1 ]2 r2 q4 Jdrop table tmp;
1 k' x: p0 j* P  H5 V  ]create table tmp
/ t+ }4 C( z6 t- W, M(
( {7 d4 M) Z: l5 t3 t  u[id] [int] IDENTITY (1,1) NOT NULL,
8 ~0 t% X6 Z" U0 L# ^- e, ~' g[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

: z0 J0 s& y. y# f: fdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
$ |# f- @/ G( Hset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
% {5 q6 R* ~8 b3 T: g, Vinsert into tmp exec master..xp_dirtree @root,0,0
; _7 Y( S9 R/ l+ g' F; D7 {1 \* Pset @id=(select top 1 id from tmp where name=@name)
5 p8 ~! t& m$ ?9 }1 Qset @depth=(select top 1 depth from tmp where name=@name)
1 S( ^/ Q9 M9 A( ?* N* dwhile @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
( b$ I/ N! q0 [: W% w$ lset @depth=(select depth from tmp where id=@id)
8 u( m3 w- x( H7 a# M+ R' T+ X$ Bset @name=(select name from tmp where id=@id)+'\'+@name
  r. w0 U  t- Kend update tmp set name=@root+@name where id=1
select name from tmp where id=1