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

admin

查找文件的语句
' ~8 T/ _4 ]+ m! t. d( _7 |
CODE:
6 u# j1 a# C! X- p3 g; E! b$ J9 n
drop table tmp;
/ o4 i0 R5 U( }+ S0 ]5 S" vcreate table tmp
% m) f! r2 _, l# ?(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
# N% R" E0 c2 {8 N4 \: f);
: s, L2 M/ |. H  h3 Z. O/ _2 S
: I2 q/ H& M  J1 rdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
1 d8 L8 B/ d# D; k9 n0 C3 Iset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
; m" P! L* |5 bset @id=(select top 1 id from tmp where isfile=1 and name=@name)
) ^) A& h/ S9 ?8 R2 o* R: a# \: \set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
* j6 m( X* ]/ H) rwhile @depth<>1
' d# s9 E' q# R: pbegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
2 n. I: l0 K! i. f" x+ [set @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
! ]3 I3 l4 z6 W! ^end
' J6 v" F7 U* {; Yupdate tmp set name=@root+@name where id=1
: Z8 H5 J; _+ S4 \$ l2 p3 b9 @select name from tmp where id=1
  U+ a2 z6 D! A7 D7 X! ^. m
查找目录的语句


CODE:

5 G$ u6 Q0 |# _; U# s: b! x% q, R
, N! L: n8 {. v' J0 Q* y. X/ O. y- O7 jdrop table tmp;
create table tmp
(
  b* U6 B& l2 W: k# B[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
# z+ b! z& z3 ^+ Q' r+ X);
; x' x" H" w" [
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
# k& p* _; b8 `3 b& {set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
2 w& f2 Z9 {& r- {2 |7 {7 {- Twhile @depth<>1
begin
  `: l/ K5 V4 Yset @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
* |( Y# Y1 B* f, x1 x% o! Send update tmp set name=@root+@name where id=1
select name from tmp where id=1