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

admin

查找文件的语句
& o! I# W3 C3 Q
# [( O4 _7 t& h- PCODE:

9 R  ~$ K; ?% {1 T8 w7 z: Rdrop table tmp;
create table tmp
( L" g$ X7 |" t, G1 K- F(
0 i8 A  ?+ O+ Y- }[id] [int] IDENTITY (1,1) NOT NULL,
# I: i8 d* c1 N9 T- l) P- S0 Q[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
3 X" G. w6 y, u, p' i, I. K);

- E8 J! y1 j, P8 gdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
: }% {( l( M8 I; H: oset @root='f:\usr\' -- Start root
: M  `6 j4 L. r9 ^5 B8 u/ [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)
& w9 M+ @& ~8 @" Uset @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)
set @depth=(select depth from tmp where id=@id)
5 ~. i) Y, k% x$ B0 U! zset @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
: p& `2 M  {7 _select name from tmp where id=1

2 L# b; m+ T6 ?; U查找目录的语句

  P$ m( R$ x* G4 L
CODE:
5 \, ]) f5 x: p- i  T3 U1 J

- H3 R% a: Z7 M+ q* Ldrop table tmp;
, e, _* C$ Q" X; B( q( D* Rcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
% Q0 n- K' m  _2 I" F[name] [nvarchar] (300) NOT NULL,
8 @  a- B& ~, c) `4 L[depth] [int] NOT NULL
);

3 U8 b5 r0 ^. d) {8 Rdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
. H0 r. C% H$ v( Hset @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)
& O/ H9 [# v8 O* T* Z% l( Gset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
8 ~3 G! V+ n0 S8 G8 |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
& A$ D3 V: ^9 L4 g6 Jselect name from tmp where id=1