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

admin

查找文件的语句

CODE:

7 Q* Y) g. n6 c0 e$ F3 W% O6 ~: Udrop table tmp;
! H3 h1 i" W  k9 D9 V+ Gcreate table tmp
(
' d0 V- R9 L* Z: Y$ K[id] [int] IDENTITY (1,1) NOT NULL,
, i- A$ M/ u! C$ y1 Y" b$ m! M[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
' d% D# r+ @/ K$ r" R8 p; [2 N6 h[isfile] [nvarchar] (50) NULL
8 h) l$ ?. [# p  k7 D);
" c- d; j9 P4 A1 p) n
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
  B6 Y( G8 b; K) Z7 v; I8 \set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
9 t" c( G4 ~9 G8 Jinsert into tmp exec master..xp_dirtree @root,0,1--
2 M7 W, G) A7 x% y! Xset @id=(select top 1 id from tmp where isfile=1 and name=@name)
; K8 k6 o) [; J; I4 Cset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
begin
9 ]: P0 B: G9 _* Z- O  y" _& uset @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)
set @name=(select name from tmp where id=@id)+'\'+@name
$ f1 b. c: ^$ i1 yend
: B' |/ h1 z7 T0 R: cupdate tmp set name=@root+@name where id=1
1 Q6 r2 a3 W9 S) p' K5 u2 Kselect name from tmp where id=1
$ c% H4 D. [& u' T4 K5 l
( y3 X7 e! \6 U% B查找目录的语句
1 J5 j7 S* w* O- o1 X  ?* E# y$ J! N
) K5 d2 Z. p( I- b
! K  V: F! }" O: C; z& `CODE:
$ \0 }% L: l5 s4 t4 H
; V7 W- X: s2 L) r/ {  L5 Y
  Q0 g3 K1 Z% \7 A1 G% V) S, `! Ydrop table tmp;
$ Z. g# U' M0 v7 y3 |4 }! q$ Ycreate table tmp
(
& c" B  Q( f5 X: }% {[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
' d; i3 {. \. P9 f8 H6 s);

/ E. X6 F: P0 W; Xdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
: ?! P: p( o" k, |( cset @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
7 o" c) s. o1 `9 Q; @& v$ ^set @id=(select top 1 id from tmp where name=@name)
  m' W8 k7 L/ Z& \3 V# @+ W2 ~$ [set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
7 h+ C" Q  T. vset @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
; x1 o$ K' q- Q8 @0 P- }! `end update tmp set name=@root+@name where id=1
select name from tmp where id=1