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

admin

查找文件的语句

CODE:
9 {" T3 e; ^5 R) e" N0 Q8 `
drop table tmp;
create table tmp
) r* f! G' u& h- M3 j+ i) C: g(
/ w! A0 `) Y3 R0 E* I[id] [int] IDENTITY (1,1) NOT NULL,
" }/ J- o1 _: ?) H8 }[name] [nvarchar] (300) NOT NULL,
# f, A; m/ j4 Z" j& ~[depth] [int] NOT NULL,
! f3 O# y; [* M  O[isfile] [nvarchar] (50) NULL
);
: o. e# U  j4 d2 \% e) s5 c
8 S; r' i; @- _/ Udeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
7 P, J2 B" ~$ ]" Rinsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
' ]2 g; {0 U5 p  Zset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
- E/ u1 U7 s; D2 e! D/ E0 K' d/ m$ awhile @depth<>1
6 f5 q# {" r4 b4 m: _: @begin
8 ]+ s8 {( Y. pset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
! q5 z0 i/ V3 Qset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
2 u1 |9 u- v; j2 Mend
update tmp set name=@root+@name where id=1
select name from tmp where id=1
* n# c) v. L5 y3 ?7 o
查找目录的语句
) `, O2 X( g, t& o2 R
  J2 {9 J' A! H' A  B8 m" ?
) a' e/ \8 Z7 h: XCODE:

2 E& E1 A( {: w% V4 H& K
drop table tmp;
. @4 O$ v2 G; U: Vcreate table tmp
, E+ \) ^9 t% U. e/ H6 O(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
/ s; v" |  m  u" q
4 B) ^# y1 A- e4 \' Qdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
( _/ I6 \* N2 P+ D9 h# R7 @; j  W; \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)
$ |# M; f$ \, M% K8 zwhile @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
. }/ v( u4 f/ }set @depth=(select depth from tmp where id=@id)
8 Z) b8 |, ]4 cset @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