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

admin

查找文件的语句
$ J+ b9 P/ J- z% v
CODE:
# n! D% _( y/ Z% v$ P; }* q6 v
- B' ]; ~5 x: _8 hdrop table tmp;
* V* c" H& i* p& t- c  s, H1 lcreate table tmp
8 U6 Q/ h3 G1 P( Z5 H(
[id] [int] IDENTITY (1,1) NOT NULL,
9 @: m! ?& M8 ~# Y7 w6 M[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
) f7 ~& A+ R6 B: G[isfile] [nvarchar] (50) NULL
' E, C4 u* ~* n, s2 h8 j8 E; z! b);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
: v9 ^; L: F7 O; Sset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
! S1 I" M6 B; qset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
5 P& J3 t8 E& P3 t1 Q( Pbegin
' P( D% b2 E% bset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
/ @+ A! E! g; _0 g, \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
7 i; ?8 Y6 E6 f+ O& Oselect name from tmp where id=1

查找目录的语句
0 g# J, `& A3 S' u
6 r; I4 t- b4 j0 B
( f9 b' Z7 j; n. R0 Q' WCODE:

( u" @, G# c+ c  c
2 W+ A2 H$ b5 B+ }0 j; l3 tdrop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
3 E' L2 G: q  ~) m- j[depth] [int] NOT NULL
" |2 L; x! w9 p4 W% E);
2 j% g  Y$ l8 B8 ?3 }
! g2 ]) J  ^; k9 Z) H' |4 ]! _declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
$ y7 H& W& h- g" v4 `set @root='f:\usr\' -- Start root
6 S+ o- p, h8 `2 u" aset @name='donggeer' -- directory to find
! o( W* [+ u- einsert into tmp exec master..xp_dirtree @root,0,0
7 b: M) b& V% ~set @id=(select top 1 id from tmp where name=@name)
' [; ?) v" t% E8 Cset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
  ~2 T  Y  {' o, k$ R/ |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
: R- i/ w! H& kend update tmp set name=@root+@name where id=1
select name from tmp where id=1