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

admin

查找文件的语句
9 w# T5 w/ K+ k
CODE:
( m+ h3 q" u' |! T. f
  H5 ^3 b; h. O/ Z6 hdrop table tmp;
3 D/ ~. Y' f, H* G# u! u. Wcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
4 G% x' k: B: z( K[name] [nvarchar] (300) NOT NULL,
6 K; o- Z0 k+ ?& }9 b[depth] [int] NOT NULL,
  B" t& c& n( K$ \! ?[isfile] [nvarchar] (50) NULL
: l4 i4 {& f0 U);
8 b4 e; P7 S5 O5 r( ^: |: n5 Z$ _/ x
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
& D7 n& _" d  Qinsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
: \( F$ ?& E& Z% G9 Pset @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)
4 t  ?$ |# i8 `' yset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
  {5 k3 l) S! [0 }- A/ Bend
update tmp set name=@root+@name where id=1
1 B) a9 X; B( A0 |9 [) ^' lselect name from tmp where id=1
1 N$ Z0 X  c" {' t! B
, [  i  ?# r: X" O' b  R4 A2 R# T查找目录的语句
/ F% T& @# R# b7 V

CODE:

. @$ l. \0 P% |+ F* U
) Y1 U. k1 e. }1 T% G# a+ b5 j+ I; V: Ldrop table tmp;
7 Y  @; ]9 D8 Q  n- d6 @create table tmp
(
! V( }7 j% z+ r[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
$ j+ m. o+ E, c6 ^: h5 T[depth] [int] NOT NULL
( Z( u% h# K* ~);
' L* N6 Q- q" ~4 |. `( J
5 [0 f3 [* k0 [declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
$ s% T/ b& J$ J0 S; l6 vset @root='f:\usr\' -- Start root
. O1 p* c- i: ~3 j; P6 `  p. f  rset @name='donggeer' -- directory to find
* N8 m  `4 B3 |/ G, |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)
1 q& @$ p5 E9 ?7 [9 }+ c7 zwhile @depth<>1
, U  a4 Q$ U9 n& U8 |' Q0 {begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
) i" y  J) }5 ^  G9 Wset @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
( `9 h2 ~' ]- @select name from tmp where id=1