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

admin

查找文件的语句

CODE:

1 G" t) ^/ S* {- ldrop table tmp;
create table tmp
(
5 _7 \/ [' @- `# O5 U! d$ j[id] [int] IDENTITY (1,1) NOT NULL,
7 `- }- N2 @, N1 m. Y[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
2 ^, r. \5 j" [. l' f
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
, [: x2 U0 |4 J4 i6 h+ R& e  `set @root='f:\usr\' -- Start root
' y+ f* P' Z( B; Q3 C% J( c' i5 Tset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
5 p, e, z+ H0 A; z# K4 Gset @id=(select top 1 id from tmp where isfile=1 and name=@name)
set @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)
  v! q! b  L' H1 D3 i% qset @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
: x" e/ A' \4 t) U9 d% Sselect name from tmp where id=1

2 b* a) p, E/ N6 q2 z5 b# C查找目录的语句


6 x$ H- S% |: R7 u" HCODE:


drop table tmp;
/ A1 n- K' v; ~0 k/ ucreate table tmp
( c1 G  q% T! W+ W(
[id] [int] IDENTITY (1,1) NOT NULL,
0 H8 U3 z; j6 C' y& u) `[name] [nvarchar] (300) NOT NULL,
7 k. y% a+ H" H% J[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
* c* Z5 h; O& k7 z6 Oset @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)
set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
& W7 [: L$ B. U3 ^3 g6 Rset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
: M6 E( F, d; h+ p# O' zset @depth=(select depth from tmp where id=@id)
( U1 Z6 G% P" `; o1 C: i, }set @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
" d7 e5 k+ r' n# ?0 yselect name from tmp where id=1