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

admin

查找文件的语句
' q0 p+ t4 X+ ?
CODE:

drop table tmp;
6 @7 N! P0 m( d6 K3 |% v, \$ N  d: Icreate table tmp
) {% x+ N% q, \1 }6 g(
[id] [int] IDENTITY (1,1) NOT NULL,
' v# f, b6 _$ h& w8 l+ w! N8 c3 U[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
' B' l; Y0 m9 ?. f$ V' L
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
  _- ?9 r/ W5 M' Y* x) {set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
3 }- s, J% Q0 x' P4 @% I* [3 Linsert into tmp exec master..xp_dirtree @root,0,1--
7 N  }% `& h: o( s3 R/ h2 ~4 kset @id=(select top 1 id from tmp where isfile=1 and name=@name)
* Q6 k4 A# T" Q* F# Wset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
# J3 F. \) _9 R" k( [while @depth<>1
4 r9 q8 O8 g  E) z: fbegin
1 c3 d. \& I) R. Z& ?set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
) A' y! m) Q+ d/ l# d; o3 Cset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
end
# b4 O; u$ J( E( P4 ^update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句


CODE:
+ i1 k" L5 r, ?' K5 i/ n$ [
3 [5 |( b) l$ t$ ?
drop table tmp;
create table tmp
) H+ l- c1 Q% [4 f+ r(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
2 i3 r5 B! G5 @! d; n$ T0 V[depth] [int] NOT NULL
);
6 ]+ {- ]+ E& j$ F( D
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
set @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
" W9 H' \5 i, J5 h; owhile @depth<>1
begin
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
end update tmp set name=@root+@name where id=1
select name from tmp where id=1