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

admin

查找文件的语句

% m2 j+ Z& o! p" _" lCODE:

drop table tmp;
  }: z9 t+ T7 b; r4 ?7 G1 ^create table tmp
9 ?3 N# q& w5 ]5 l( J- M) y/ F(
. ~! c' P. P6 Z- i. g& }" H[id] [int] IDENTITY (1,1) NOT NULL,
: d" R" p8 G5 [1 s) R& K" O$ T[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
4 H2 w/ z8 s/ N$ g, A* L% a[isfile] [nvarchar] (50) NULL
);
9 C- r) v! _* n, G9 T
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
' p' G/ i& H( O/ binsert into tmp exec master..xp_dirtree @root,0,1--
8 n! v! Q% h4 s5 t! V7 Q; Y* Oset @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)
0 k, @1 b0 x. @( jwhile @depth<>1
* _$ {" B5 L: ]begin
$ t: n: U' l  I" s" _$ A8 yset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
: u0 ^! \' Z! e# qset @depth=(select depth from tmp where id=@id)
+ w1 N6 B0 l% U- \  Rset @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

! Y$ I* _, |( K( j5 ?查找目录的语句
- k. t4 {0 t! D- z" B9 I; b* R
, `# E: M- j) Q( b' q- g6 u6 V
CODE:

& C1 E2 u. U- ?+ @9 o
drop table tmp;
" V" z. z& C" y2 D: ?( x9 A) H3 H2 }create table tmp
& k8 C5 }& j' W. p$ ^7 q3 D" ~4 E(
( j0 M- P1 @) K  |+ n2 s* q$ f[id] [int] IDENTITY (1,1) NOT NULL,
- x+ w. ~$ J+ e( Z0 A8 [& N$ `[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
" j0 m2 ^4 d3 c);
6 B1 ?( U, i, ~* C, ?
1 B. |( _7 D( ?% ?; C  h' Hdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
( V1 }/ S0 C/ K/ n) N8 ]4 L# iset @root='f:\usr\' -- Start root
7 K6 m& v) y7 o, y* f0 A7 {+ c6 d$ Cset @name='donggeer' -- directory to find
9 L' ]8 _3 S3 J9 J% \. Iinsert into tmp exec master..xp_dirtree @root,0,0
9 z" A1 J! c- k/ ]9 p2 eset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
1 f8 X8 O, {) Q* F6 R) Dbegin
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
% w" a( \  J! r, wend update tmp set name=@root+@name where id=1
& K% G) d- P+ q5 h0 `  Z7 {select name from tmp where id=1