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

admin

查找文件的语句
& i( J8 o+ _1 G
! b3 |/ D9 I4 o. hCODE:

7 s: n9 I. E+ y8 Xdrop table tmp;
create table tmp
% R% e5 e' @! u" i(
) N* x8 r: C$ ~1 m[id] [int] IDENTITY (1,1) NOT NULL,
2 M$ ^' n% p7 {, T0 e7 [[name] [nvarchar] (300) NOT NULL,
8 a' }. \( {; w/ q3 ?' r[depth] [int] NOT NULL,
' \+ V. y$ [& ]: u8 a! Y8 e3 j$ V[isfile] [nvarchar] (50) NULL
);
+ b* [( S. S' r, L! a6 _$ j& d
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
* F' q" @9 ~9 n% Xset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
6 g, l9 i% T  U- Rset @id=(select top 1 id from tmp where isfile=1 and name=@name)
( R0 |, K5 O" S, ^  r9 Vset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
2 M0 e, q% q; x+ K. u$ ?7 qwhile @depth<>1
begin
: g% \+ A* [7 K/ vset @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 }. b/ G2 [" `+ Yset @name=(select name from tmp where id=@id)+'\'+@name
5 {! {, E% N* h/ P! d0 l/ n/ Dend
update tmp set name=@root+@name where id=1
2 ]7 s% S1 z& f+ a7 H2 ~" l, Lselect name from tmp where id=1

查找目录的语句
& P# H; ?5 u- w: N
6 g- y. t( ^5 d; W: @
CODE:


drop table tmp;
create table tmp
- t7 b; O) j. v; t7 q(
[id] [int] IDENTITY (1,1) NOT NULL,
: X5 h" m' Z- f1 R[name] [nvarchar] (300) NOT NULL,
8 f* t  t% [2 X' m4 M" K2 u- c[depth] [int] NOT NULL
);
. |' K* F; {5 ^
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
* t1 W3 z1 k" L$ p! pinsert into tmp exec master..xp_dirtree @root,0,0
! y' L9 T9 r% T- H% n) O$ Uset @id=(select top 1 id from tmp where name=@name)
2 `. n; _5 ^1 j, G7 j* N1 Qset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
& u" `- Q# n$ r  h9 q6 gbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
9 H1 z) p# C% q# E7 ]) iset @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