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

admin

查找文件的语句

CODE:
" i1 i9 u9 b& K* t8 Q* F
& H/ C1 [" S3 g' g/ z% o; E8 [& f1 v% ^drop table tmp;
  {; z6 V( z3 }1 E5 Qcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
& t+ M$ [3 \  ?, Q# s- Z; g[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
% R1 l7 S* W( ~/ |, U);

+ |! e  Q* h, u9 c, pdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
: j2 P# K5 P) b' ~  i. K9 Y' aset @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
7 ~* k  B: c9 v& ?1 Y( Ubegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
+ u$ m$ a4 A$ W6 _7 nset @depth=(select depth from tmp where id=@id)
  H3 i1 j- Z9 ~0 ?" oset @name=(select name from tmp where id=@id)+'\'+@name
end
) R4 q5 @- M: \update tmp set name=@root+@name where id=1
select name from tmp where id=1
% R# V$ J% e" N; I
查找目录的语句
, F( M+ m; i. _3 E+ M! b/ ~  N

0 F- f8 f: [8 n9 N" sCODE:

8 H7 |: ?, p2 b/ U
drop table tmp;
8 Y8 r# E9 v# A" f5 c9 wcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
+ ]5 |, h4 s2 j% `[name] [nvarchar] (300) NOT NULL,
; A' n+ \3 |& y  a& n[depth] [int] NOT NULL
- [  a( t- r. L6 @* I& `);

9 y6 H* k$ I# f" m$ Edeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
) K& H( a( k  _' Qset @name='donggeer' -- directory to find
! l% p. X* i/ Q0 r3 s) Kinsert into tmp exec master..xp_dirtree @root,0,0
5 y9 E' R% j% h( j2 ^7 cset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
. i+ L, g- I* @5 s! T) I8 qwhile @depth<>1
5 c9 T& \  e, I. ]2 R( Ebegin
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