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

admin

查找文件的语句
  j  q' a/ v4 b: q% c0 _
) X. E7 _; |6 Y, o8 p5 z% M3 ACODE:
4 g" {$ o2 J. L3 f
8 T8 C0 @* {5 A  ldrop table tmp;
. Y; c) o1 [/ G0 Q' c2 pcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
2 Z7 G! F) i+ j" }9 u4 mset @root='f:\usr\' -- Start root
5 s- G: S$ S7 B3 V- E5 @set @name='cmd.exe'   -- Find file
1 j& h6 P& j' ]insert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
& y/ I" p7 G" K. Uset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
! I% F# R9 _5 ?6 {, }0 Wwhile @depth<>1
7 u" x; T0 p; V+ x% Z% P: j( [begin
# E* `$ l, {5 o& T( Q8 P/ m/ H& cset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
) Z; _: W* g4 h6 N( u# fset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
; R1 G1 ?9 P" Q3 U9 C* ?+ fend
update tmp set name=@root+@name where id=1
4 F* R1 R8 c* E: D4 h: h4 gselect name from tmp where id=1
$ |: h5 C) @, o, N
6 _5 D) I; k) c0 k查找目录的语句

3 D  t9 K. V* a: H: G8 Y
, E6 f- e: M6 ]6 Q: H( Y5 T, ^CODE:


drop table tmp;
0 |! G& Q7 |  N2 j: ~- Ocreate table tmp
(
8 ?5 S& n/ \$ H8 d[id] [int] IDENTITY (1,1) NOT NULL,
1 K5 s" K- P! U1 t8 G3 q  S4 V[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
% d  l* ?# `4 U; _! \. F/ H$ B1 ~
+ A2 e# |; q; i( n' bdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
- ~( Y( r; i0 c& V- {# Vset @root='f:\usr\' -- Start root
9 S% S- D3 f: h9 S1 C8 H/ tset @name='donggeer' -- directory to find
  p6 g1 u# N8 W" z4 ^( \insert into tmp exec master..xp_dirtree @root,0,0
0 b1 s" j/ E$ m4 s# vset @id=(select top 1 id from tmp where name=@name)
  n) [8 j; p' s% S( [set @depth=(select top 1 depth from tmp where name=@name)
! g- ?. N6 B5 ^% m- x% {8 [while @depth<>1
+ B3 ?7 ^+ |! o% Mbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
6 k/ m. H* H, |. ~2 Zset @depth=(select depth from tmp where id=@id)
6 J6 m( w, }* ?3 g! Eset @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