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

admin

查找文件的语句
' s; M( c# [" ~$ A1 x/ K
# X3 X- t& b8 {& hCODE:

  f4 Y- @" i6 {. ]0 P6 Fdrop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
- H% f$ U4 e, U3 O. B0 ][depth] [int] NOT NULL,
7 H( r* {0 @9 t[isfile] [nvarchar] (50) NULL
4 P$ J8 R2 C" H  ~7 Y6 }1 e3 J);
5 K  q8 Q. x5 ]  L) j  P
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
1 p4 h8 _) X% Q! kset @name='cmd.exe'   -- Find file
- }  K* X6 H( einsert into tmp exec master..xp_dirtree @root,0,1--
' P3 X- [3 [. b# w7 zset @id=(select top 1 id from tmp where isfile=1 and name=@name)
3 s9 R+ _2 g0 g) {3 iset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
% }1 Y2 d4 X4 N& {) Cwhile @depth<>1
begin
- X; H, G7 z4 {$ v6 Q- p. Gset @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)
- d0 D! r% k1 Iset @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
4 \2 _! Y. \* f- wselect name from tmp where id=1
( }5 K) w% S% L$ ^. X
" }# m* [6 j0 c) F+ P) S( c/ B查找目录的语句
- ]9 y% E/ e$ i, `  d' T/ a; K
6 o# u' V1 m* Q/ {
' F5 E( R: j. E; H; W( u; A; [CODE:

+ w; D- O% F) ?" W! j/ Q* J9 W" G& W
drop table tmp;
# ]) G, V5 `5 c- \( t) x- Ecreate table tmp
(
: l3 Q8 @' w  n1 f% K" G! w[id] [int] IDENTITY (1,1) NOT NULL,
; P, _0 D' n8 P[name] [nvarchar] (300) NOT NULL,
. Q" L. b9 A/ f6 i5 X  N& K% m[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
( R; Y, [) f6 ?. Rset @root='f:\usr\' -- Start root
% ^3 W4 d% x1 P6 c" J0 n7 Yset @name='donggeer' -- directory to find
# _6 x2 o: S: F$ Kinsert into tmp exec master..xp_dirtree @root,0,0
+ k! j( j; b" t: z0 ?set @id=(select top 1 id from tmp where name=@name)
set @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)
- |( ^6 o( n5 }2 w' U; r& uset @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
( k9 a( ?' I1 W" f2 p/ L8 Nselect name from tmp where id=1