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

admin

查找文件的语句
: p5 O5 r; m! a; _' ^" o- Z! }+ C
- v  \' O& i( G- ?. gCODE:
. m! v. O9 ?" R: Y/ L) q9 q/ |9 N
drop table tmp;
7 L$ _! b8 E4 y  I) l# _create table tmp
$ {) n# ?  F/ f$ Y(
  Z" I# k6 V! f" p: n; F! d! K[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
  o0 Q$ \2 E: P; b; X);
$ k( k& P( ~& N: x
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
! h0 f% i5 q9 e1 n' U! fset @name='cmd.exe'   -- Find file
6 }' ~7 W& ^2 z) l5 R& ?2 m; }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
& ]% ]& r4 ^  G6 |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)
set @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
. v+ o4 W- k$ x4 vselect name from tmp where id=1

查找目录的语句


6 X) p0 X5 i* t0 TCODE:


5 s7 o3 O) k2 T0 [( ydrop table tmp;
: ~. z2 n$ w) `3 ?" O* c2 A1 Acreate table tmp
/ S% ], l" L1 H, R1 e6 ]' j; F(
5 @6 X4 e3 {- {$ I[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
5 S( }' Y' Z- c9 o2 R3 C# l& d
" [% _- Y* [0 I( Pdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
: L: N3 N% q5 t. f7 _9 `; Eset @name='donggeer' -- directory to find
. w% ^( T" o& l& V) w. L& P6 D1 _insert into tmp exec master..xp_dirtree @root,0,0
( ^- T; o/ v$ G9 v* B- V, z, \) D8 ^set @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
, J8 ^2 N0 a' ]: C, lwhile @depth<>1
. E/ q8 A6 J9 J8 {- dbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
4 [: W- X! k* s' @set @depth=(select depth from tmp where id=@id)
! f1 `0 h) K6 Z, W# h# Tset @name=(select name from tmp where id=@id)+'\'+@name
! a+ A2 P+ q% Q4 Xend update tmp set name=@root+@name where id=1
9 v, v+ F+ ?2 mselect name from tmp where id=1