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

admin

查找文件的语句

CODE:

drop table tmp;
create table tmp
- q* {9 y4 Y& a. a4 Z1 T' k' G(
/ O* y( r! e( l7 E0 |3 t2 M[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
7 @& |, V4 {5 d3 r6 p! e8 T[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
) T5 R4 X. ~5 O4 E9 R- K% k) U. N2 oset @root='f:\usr\' -- Start root
& Q5 e# k# V7 E+ R. B) s$ B4 ~set @name='cmd.exe'   -- Find file
" d$ b+ j4 q0 _! \; ?+ iinsert into tmp exec master..xp_dirtree @root,0,1--
! i. w! E8 v: s7 z3 H; }. w1 Qset @id=(select top 1 id from tmp where isfile=1 and name=@name)
) R1 V! m: ?) m: g. Kset @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)
* b. s( w" p. k) nset @name=(select name from tmp where id=@id)+'\'+@name
end
7 s/ a0 l6 T9 Qupdate tmp set name=@root+@name where id=1
, ?9 T- ]3 o# v/ q* \, cselect name from tmp where id=1

查找目录的语句


1 S+ N( X. E% u: N- c, y) e. TCODE:

2 E7 I- p4 e( v; j( X
3 l& F5 A6 @" ?# z3 z! ~) z0 ndrop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
! n3 {$ k8 j0 S[depth] [int] NOT NULL
5 E* a  R5 H& `4 v0 e);

0 |: I0 r5 ?- Rdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
% s% ~1 A. I2 P6 F1 C  oset @name='donggeer' -- directory to find
& ~- x; z7 G+ }2 ?/ [insert into tmp exec master..xp_dirtree @root,0,0
' M: E& V: L& I  e. g" e* mset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
) }! e9 C, P* Z$ Awhile @depth<>1
5 v" f6 f! m# _! X: s5 m. c5 Gbegin
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
select name from tmp where id=1