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

admin

查找文件的语句

, T: a3 {3 ?2 I. J7 |+ i1 K; g2 XCODE:
8 |0 G3 _+ o& m9 M% D
3 P! b7 }% F' _" Z" Kdrop table tmp;
create table tmp
9 ^. d# ?% f; S) u(
  C4 H) z3 M! W4 K+ o! q; h! @7 ?[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @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)
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)
) J7 `  @8 c* j( R3 y) S7 I: p' [set @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
' U3 a, m# l1 t+ Iend
update tmp set name=@root+@name where id=1
select name from tmp where id=1
, O/ B# h, v+ ?& i8 d
查找目录的语句

0 ]1 a3 |& A+ C# G
) t7 X2 H7 J3 ?+ }CODE:

2 I% @3 O8 c0 Q
drop table tmp;
: n) L6 @7 f5 H! h  q9 W& Xcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
2 s( g1 }3 ]7 _. b9 Y' h[name] [nvarchar] (300) NOT NULL,
" U' y: P3 w' I: t! b1 H: a1 G# E. P% W. ~[depth] [int] NOT NULL
);
6 J' i  K/ S: z) d
, G) O6 |/ W& C) N' fdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
5 F7 g& p4 s. [  q) ~set @root='f:\usr\' -- Start root
/ e& A: B& a; o; [! }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)
( ^1 }) s1 B8 I2 z4 ?3 ]  Iset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
5 Q9 a. l" H1 ], Pbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
, B- W; ]! b! p6 `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
3 P4 M& N4 }5 {5 G# Jselect name from tmp where id=1