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

admin

查找文件的语句

# p2 z- o( U; x! Y; |/ S3 h6 ?CODE:

drop table tmp;
% y) M2 r0 k8 Q" \' N2 g8 Gcreate table tmp
: r$ [( v  n7 G(
/ {7 v' Q) |; n. n6 k1 F0 e[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
" \0 A" A! r1 ^1 f);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
' R" M* u+ G' J4 h1 E/ S7 Tinsert into tmp exec master..xp_dirtree @root,0,1--
/ K. ^3 P; ~% I/ S+ S4 \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)
8 k. n8 a4 a, h. q1 a9 awhile @depth<>1
8 h5 D7 i3 |6 H- s9 i& n: h+ U& bbegin
0 p! x) ]. N2 J& sset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
% ?6 u* W' h3 |& h1 K2 R" J% Mset @depth=(select depth from tmp where id=@id)
( D: U- L/ f& ^3 }4 Q; e& w" wset @name=(select name from tmp where id=@id)+'\'+@name
1 Y0 S2 J+ N# @2 H# W4 mend
update tmp set name=@root+@name where id=1
4 P" l1 o- ?: R. o5 D& _+ h2 y! ^select name from tmp where id=1
9 O. I) m' m6 G$ X1 {, n+ D
. x) D1 `6 u: Z2 T8 @查找目录的语句
, x/ c6 Q  `  t* g: e: H! X+ O  }

/ Y4 d8 K( i0 a7 l* ACODE:
0 P  a, z7 }- z8 S

% U# Q. O/ y: h  S: Pdrop table tmp;
6 |0 w0 U0 w9 k" i& W2 r) e9 ocreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
# n4 f1 o6 E( c- S1 D6 y4 t);

; Q  n/ i$ N( G" Ddeclare @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
1 }0 C, q6 g! |set @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
  V. y) s; r, G2 p" b/ T" ^while @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
" V8 s- f+ O) d7 L) h0 N/ Mset @depth=(select depth from tmp where id=@id)
% w3 X$ h. j0 f1 l  _& |- u0 k1 Kset @name=(select name from tmp where id=@id)+'\'+@name
' y5 S6 V9 \% j- t* V( f7 send update tmp set name=@root+@name where id=1
select name from tmp where id=1