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

admin

查找文件的语句
1 ]4 H6 A- N. X6 ^8 p( ?
! X8 r) Y. C8 [7 a6 DCODE:
) W% b; W1 O4 U: ~6 ~$ x
drop table tmp;
0 i& T" n; v8 _create table tmp
$ O1 F# `6 W5 N% C: I(
  u' n3 ^3 B4 B& d[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
. b; {8 B# \+ T  P: h);
0 |# u0 X2 ]( Y4 ~2 ?3 C, W. n/ w3 c
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
( l) C6 O8 n; l0 F  v: dset @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)
) c9 f- U/ k. Q2 ?2 |8 ^4 g  ]set @name=(select name from tmp where id=@id)+'\'+@name
/ N4 H/ V& ~2 Hend
2 U  {8 n- \0 m  k8 ?. B+ |% ~, Y, Yupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

; ]7 }0 r$ e- e3 Y) A; e查找目录的语句

! w; e$ z3 s, {' n( Q: ^
CODE:


2 C/ Z# a- U( [7 u) udrop table tmp;
) \3 |8 U8 m5 s, a. mcreate table tmp
* @+ F9 l; ~. s+ y' l  W" L( W' b(
; g- t1 t" f) Q+ c* Q$ ][id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
9 `- x( ]' ^. y2 W[depth] [int] NOT NULL
);

1 K, g  R" _7 A' w2 Adeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
" P# f) Z- B6 f0 `  \set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
4 e) e4 D7 V4 P3 J5 Y6 f, [7 F3 Xinsert into tmp exec master..xp_dirtree @root,0,0
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
& H9 T$ a5 _+ S$ s- z* Q. @; `0 Kbegin
- F5 S6 M- e0 ~0 S- dset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
) S- X; E, I3 ?/ Vset @depth=(select depth from tmp where id=@id)
  f: j& F6 C% s. Y6 F0 nset @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