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

admin

查找文件的语句

CODE:
7 j: Y, X  n2 _; `
1 `, B  Z2 U$ S' q' Ndrop table tmp;
. n: `7 S. K; R/ _  Hcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
, l& x8 V9 F' Z9 {3 K, Y6 I3 [5 T[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
6 k; Z2 ~" F$ A  K" R);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
5 l- o0 m( A* T% n/ ?3 oset @root='f:\usr\' -- Start root
8 R4 r. i4 S9 u: P0 m4 Iset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
6 Q' _" q4 `7 s0 ?! C7 Y; S6 v, Vset @id=(select top 1 id from tmp where isfile=1 and name=@name)
4 U+ c: y8 g! b9 m5 w: F3 Jset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
  U/ y9 O$ ~! F6 p& 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)
0 j4 o% a% c6 x  Eset @name=(select name from tmp where id=@id)+'\'+@name
end
: j" v' V: T: k3 M  ]& {8 Wupdate tmp set name=@root+@name where id=1
- ]7 P: n8 D! d) Q& _4 k" O* @* Rselect name from tmp where id=1

% b& w. I+ x: ~7 |( Q! P5 x) v3 K查找目录的语句


CODE:
( D/ q2 f& ^( q: m. @

drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
+ M/ G! G4 H, {[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
/ X2 q! H; S3 l! m* r);
* p% X7 h; a* v/ l
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
) q1 a& q7 S- E9 B# Dset @id=(select top 1 id from tmp where name=@name)
% B1 J5 p( m# B/ a3 O- ?0 {set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
* g/ K8 }- B( y8 c2 ?/ J, Ebegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
$ J! ~' O* Y: \1 m( Cset @depth=(select depth from tmp where id=@id)
6 m: o! R' ]# [4 }, M# Kset @name=(select name from tmp where id=@id)+'\'+@name
' `( t9 S# |) x- ]9 d3 h  X& Pend update tmp set name=@root+@name where id=1
! w' d1 q4 e2 ]4 G2 j; b0 Aselect name from tmp where id=1