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

admin

查找文件的语句
2 e- r& `/ l2 P7 x9 w2 h
CODE:
2 `' X3 O) w3 Y( L
drop table tmp;
create table tmp
(
/ ]6 O" p) ^3 K[id] [int] IDENTITY (1,1) NOT NULL,
, N- ?  K' F+ ^' J( v[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
. s7 \8 G+ {+ o4 S( f2 P[isfile] [nvarchar] (50) NULL
);

  B- [( e2 K8 x+ ?% k' y6 P% @declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
- q) `; M8 X5 Z( q1 a$ ?$ Eset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
" E3 s* b6 H, v/ h$ a' s- eset @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)
. }" |' g9 N2 N/ E9 wwhile @depth<>1
( Z; X% J1 e6 i4 L5 e! G; H4 h# Wbegin
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)
3 ^/ S5 x, B2 N9 ?; Oset @name=(select name from tmp where id=@id)+'\'+@name
6 I/ j5 {; ]! d1 i% p! [  \end
9 u2 ~4 R5 v$ m1 S; g0 \4 fupdate tmp set name=@root+@name where id=1
4 c  O4 B% D% E2 ?2 mselect name from tmp where id=1

/ S3 E5 U$ d! _5 G6 ?查找目录的语句

, U! F4 ^+ x- s: T
* v! H: j9 v2 _( V6 x6 ^CODE:
3 W) s2 w+ j& z/ V7 _4 f
2 E5 v2 t8 i5 ^& ~
2 G3 [; L  _* w! d/ n9 Vdrop table tmp;
$ u* T  {1 ^9 W+ u) fcreate table tmp
(
& i6 W: k. R3 m[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

( ~) v! l- g% M+ I& h) x, Tdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
- K* C2 u# e3 x- c) p+ R0 vset @name='donggeer' -- directory to find
/ g( R# p8 l# ?5 F5 F% `insert into tmp exec master..xp_dirtree @root,0,0
4 P: u" I4 I6 h4 j. `7 M) @+ C2 }) Iset @id=(select top 1 id from tmp where name=@name)
3 F$ d- {0 W3 U* }set @depth=(select top 1 depth from tmp where name=@name)
3 U0 B+ M8 q& M: A4 V9 l  ?- n' l! hwhile @depth<>1
4 D! ~2 `" a0 ?! H- u" P: ?begin
2 f8 t1 \, Y6 g2 M4 Z. }$ `! ?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
end update tmp set name=@root+@name where id=1
select name from tmp where id=1