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

admin

查找文件的语句
: F% h; p' f( q' J! S
" a5 h" A4 ]4 o' ^. TCODE:

drop table tmp;
create table tmp
: l$ ?% L) r- ^7 O% ~(
[id] [int] IDENTITY (1,1) NOT NULL,
' x8 T2 T& H) u1 A[name] [nvarchar] (300) NOT NULL,
' i* N/ f6 e9 B0 k/ U3 \[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
6 F4 Q% r7 D! @- P# [
6 `' B: {" }2 cdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
/ u( J! P. |8 Y, b3 [( @set @name='cmd.exe'   -- Find file
" n# i  }  K2 _" ?1 _9 q6 V' Zinsert into tmp exec master..xp_dirtree @root,0,1--
- ?8 c( J1 M1 H4 a8 b- Wset @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)
: j  M0 D; Y5 w% B5 Z5 Uwhile @depth<>1
begin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
9 H" ^0 f) L5 [6 Z& W- S  {8 M% _set @depth=(select depth from tmp where id=@id)
3 W$ o$ N0 @. {" d: c9 }set @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
9 B: x9 Q7 k* d$ f$ qselect name from tmp where id=1

& `# L; ~+ Z8 H0 [$ V" S$ o7 ^查找目录的语句


CODE:
! `  ~* h9 a0 C1 u3 f
- p- T' E+ g& {" G7 F# k, r9 F
drop table tmp;
  C4 j# u7 q% p* J; M6 C) j) Gcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

5 x; h; [, ^, J& v! ydeclare @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
- u3 [2 d, c$ B! Q4 Rset @id=(select top 1 id from tmp where name=@name)
, P6 L5 Y' }7 Z+ A! m5 X3 o/ hset @depth=(select top 1 depth from tmp where name=@name)
) \) H7 j: l3 y# uwhile @depth<>1
begin
0 i' G8 b/ ]; w/ u9 D! i! sset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
* z0 ~- ~" E! M9 x1 v7 E" J: c5 l0 gset @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
8 C3 H& P( _7 f/ F& ~select name from tmp where id=1