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

admin

查找文件的语句

CODE:

: U  ]; i! V% X0 cdrop table tmp;
8 a- d- |/ B9 T, s/ k  q( Jcreate table tmp
  N) q( C1 @( x3 Y" P- {1 }(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
7 J2 P# x% E4 e* f" B; H);
( d8 g1 z! P. D: ?( m* g
: E- Y0 K; w4 b* p0 X6 s% Tdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
0 D! m( X! a# [* fset @root='f:\usr\' -- Start root
4 e4 Q) T7 F$ w# Xset @name='cmd.exe'   -- Find file
/ G: x. Q* a& b4 P0 f- r" j0 Rinsert into tmp exec master..xp_dirtree @root,0,1--
+ E' |) D7 C1 r0 x/ Iset @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)
, {& B: ]( q% Gwhile @depth<>1
- C+ M( Z! f% I9 rbegin
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)
, U* z& D# W2 t3 Pset @name=(select name from tmp where id=@id)+'\'+@name
& k" Q) }4 m: t3 d  y! \0 Lend
: z  k% N; s$ ~. `: t6 Wupdate tmp set name=@root+@name where id=1
: @/ k! c( t/ m5 ^2 u" b1 hselect name from tmp where id=1

8 w( I  n, V. ^; f& D8 J查找目录的语句
8 Q/ N1 d' p. s8 i- ?
+ T  Y9 F; ]& @/ u/ u. C% T
CODE:

+ y2 K" r9 s. I& Q5 S2 g# W: k
1 V' a8 s8 q: K# Z6 s$ mdrop table tmp;
create table tmp
(
& ]# u$ K5 T5 M0 }9 j[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
' ]7 k6 O" ~5 v);
! ~/ J9 L& k' [' b2 o0 a$ i  F
0 v& m- @& _* E: c/ C$ c" [declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
# o( y* w7 O# nset @root='f:\usr\' -- Start root
0 k" F" j0 o. u$ f: kset @name='donggeer' -- directory to find
& N1 d& U  @! z* {insert into tmp exec master..xp_dirtree @root,0,0
( n  l! M& {  C* G7 b0 n) {set @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
8 ?+ W; }" J) p( N1 Swhile @depth<>1
begin
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
* S+ B* m( W) C$ G0 h7 O# t4 jend update tmp set name=@root+@name where id=1
select name from tmp where id=1