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

admin

查找文件的语句

CODE:
9 D" M' z) [* N7 v$ ]' c8 @
+ W2 L& A* z" P# z. S) q  ~5 _drop table tmp;
create table tmp
; F7 @/ [; {3 p3 s9 |) v: s" R(
" g0 \. f. e4 M$ x0 A! c! y[id] [int] IDENTITY (1,1) NOT NULL,
: `3 q; v4 G  L% o: F[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
+ k/ g( V8 J( x  \6 k[isfile] [nvarchar] (50) NULL
);
" h; w# S# @# u. u. P2 o
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
# t$ e! \/ H0 o/ P3 g4 Bset @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)
- h; S. m0 m( z) R9 h+ Hwhile @depth<>1
, ~1 n% w6 ~, D6 H$ t& ?begin
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)
: W- z, Q' U5 ]' T- n. ^( hset @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
2 ?' p) a+ ?) K( G" M$ {6 \  N
查找目录的语句


, g5 f9 d8 Y1 r. R3 f* c  W+ E$ LCODE:


drop table tmp;
7 e/ s  A' S6 Y0 ?; F  Bcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
! F7 e1 x4 \; ?) r$ W[name] [nvarchar] (300) NOT NULL,
2 F; _' `" F( w1 ^' r' V! v[depth] [int] NOT NULL
  s$ a5 l$ P, ]( l, ~3 G3 S);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
7 H+ b3 N8 G5 f9 Y9 }0 j, n- A# W! eset @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)
; e4 }, N: |6 N$ Ewhile @depth<>1
begin
5 y+ J4 k2 H' Y+ x3 e; i% Rset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
, S. D! S; V8 Y( h( r# @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
6 a* L+ b+ n, |. k! S: _6 r9 P/ Kselect name from tmp where id=1