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

admin

查找文件的语句
- G5 O# J8 Z6 B7 K! U
) {1 O2 x+ ]6 ~  h2 J1 m; zCODE:

9 \4 d, T2 j, K5 Y3 i! j& bdrop table tmp;
create table tmp
(
' D& a4 v% F, u6 j[id] [int] IDENTITY (1,1) NOT NULL,
. Q- `, K( Y* k1 D' o  J2 X3 e4 w: x[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
$ U* W/ ^7 X5 B* Y; w! {5 w) o[isfile] [nvarchar] (50) NULL
);

3 i7 Q0 O, \/ z, C) Mdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
( R5 a! ^& }( |) {, V5 Y2 W. }set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
) Y- s+ E. W2 }% T3 U! m' l/ ^set @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)
5 |4 f) ^  D% r" K& U1 Lwhile @depth<>1
- H1 i0 I  }2 M  [2 y" F9 tbegin
5 D# A, ~5 |6 l) fset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
2 A# ?* Q6 D/ V$ X. X4 g$ Mset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
; V' z8 q* S2 f4 g- ~/ Zend
! o! M  {5 u) s" j: x- Tupdate tmp set name=@root+@name where id=1
select name from tmp where id=1
" \. n  J8 y9 w" H
查找目录的语句

! V) U$ [. t+ c
CODE:


% E0 e# f9 X9 M0 Rdrop table tmp;
create table tmp
* I) g3 U+ {4 |(
[id] [int] IDENTITY (1,1) NOT NULL,
4 f2 x+ l  ~( `9 e9 H- ]- w[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
  ~, m1 t, I! M7 Z3 C);
8 h& T$ |% Y# \! l1 I
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)
while @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