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

admin

查找文件的语句
8 d4 U  }, e+ J( ^: q; x4 {
) O) w) _4 u9 F9 N7 `! |CODE:
7 x* B: E7 T+ J5 M0 |, A! k
' J7 y! d6 S# {; y* Zdrop table tmp;
1 r: R  R9 |. ~6 |" z$ Wcreate table tmp
! e: C' ~4 ?7 q2 z, z( ?+ _(
[id] [int] IDENTITY (1,1) NOT NULL,
7 d2 O  Q5 x$ f: y& T- q4 Z8 j[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
2 T. J# G% @2 K* _5 F/ ?[isfile] [nvarchar] (50) NULL
);
: H! X, f2 ~( Z5 v/ l8 e5 ?3 a5 O
1 @% N+ l7 }9 Z- z5 H! C0 q; o$ |declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
" C' T- z6 u0 I* `% n0 G- I6 ]set @root='f:\usr\' -- Start root
set @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)
set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
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)
; ^- Y0 s2 E. s% m- \* Jset @name=(select name from tmp where id=@id)+'\'+@name
. ]+ S+ _% p# p8 [# ?2 M  X7 ~end
update tmp set name=@root+@name where id=1
select name from tmp where id=1

' q, X- R1 |$ r' U* s7 M查找目录的语句

. Z- E, u4 I" b
CODE:
! O1 s+ @. P, B! ]2 l

+ ?4 Z1 X9 b& G+ P9 e/ W& ydrop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
6 o  b5 @6 R) g( u8 ^2 ^2 h$ T3 B[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
! s* T8 Y- p$ C9 M* y);
0 E  {' i8 j0 W; I4 e
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
; ?8 ^4 i2 [; Qset @root='f:\usr\' -- Start root
8 I. q6 y2 Q# H% S% p& [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)
# y3 R+ X, N' {5 `" jset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
, A8 ~8 _0 {/ Q( ?3 F* u$ Qset @depth=(select depth from tmp where id=@id)
& t: y* J( E7 i# G& A+ i1 Qset @name=(select name from tmp where id=@id)+'\'+@name
4 p/ V+ d1 T# W8 C: T+ |& [% B3 vend update tmp set name=@root+@name where id=1
5 E5 _. E, _( `, w- e- uselect name from tmp where id=1