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

admin

查找文件的语句
/ a+ D3 i- s  A3 D% B7 Q1 H/ b( V
1 c  r+ r! J$ i3 M+ x+ pCODE:
! d) p: G6 B( G2 G& l
# T1 V  i# l: ?% y' N; ddrop table tmp;
create table tmp
8 v; F! e7 G+ q# _% h5 _% {% H(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
" B( l. y% i$ d);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
8 z: K7 F: B9 o! a# l) Uset @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)
1 S+ b1 j$ T/ N6 K7 r) @" Xset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @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)
  I8 O) [: v0 v5 p0 Tset @depth=(select depth from tmp where id=@id)
* s! S: R& ]* r1 l6 L, @set @name=(select name from tmp where id=@id)+'\'+@name
) \3 ^1 Q; g: x  j8 iend
update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
0 a' d- G9 j2 {- {6 u2 G4 t
' p+ Z4 D) {1 s8 B, o: [
CODE:


drop table tmp;
create table tmp
2 K) J$ E! q+ [; T3 y(
1 A! E4 y$ z5 Z$ L9 F  T[id] [int] IDENTITY (1,1) NOT NULL,
- M- v$ w, Q& Y: d0 ~7 L[name] [nvarchar] (300) NOT NULL,
1 M  \' b1 A% s4 G& w[depth] [int] NOT NULL
);
+ g- B5 E" ~  }2 _
3 s4 W; Y$ v' V; E* T; j0 Ndeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
5 J! V$ e6 F) j6 C' y8 Wset @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)
' `- U& [6 U) t3 H) ^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)
  l; ~: a* t' J: q0 s6 Yset @depth=(select depth from tmp where id=@id)
/ R. S+ G9 h1 T  l) c; ~# Tset @name=(select name from tmp where id=@id)+'\'+@name
2 B* M* z3 G: v2 M$ A) @5 w* {end update tmp set name=@root+@name where id=1
3 k  I+ h* X$ I. l. Eselect name from tmp where id=1