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

admin

查找文件的语句

CODE:

drop table tmp;
, z& p/ g4 |+ ~1 D' E- k" r, e1 xcreate table tmp
8 h  U# C4 q+ I/ C7 P* E: k(
[id] [int] IDENTITY (1,1) NOT NULL,
- b$ K+ r( K) |3 ?$ J, d[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
% g2 C' A( h7 z8 X4 F- {[isfile] [nvarchar] (50) NULL
);

$ V+ v; s' t- w1 z' `declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
0 @- J$ j% I2 \" o; Dset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
3 e1 n! l4 y5 U7 H; Pinsert 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)
& p& V# Q+ u3 t5 ?+ @# F, Ywhile @depth<>1
0 k, R" A8 y4 J, R3 Z- H; ~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 a8 P( l0 k4 h3 W7 \# R& iset @name=(select name from tmp where id=@id)+'\'+@name
end
  G" }' l9 B4 ]7 O0 d  {: r" _update tmp set name=@root+@name where id=1
select name from tmp where id=1
1 V3 N  V6 L* ?6 A# o6 V; E3 G
查找目录的语句


* Q: C) R+ u: T$ g0 dCODE:
& d- M1 q5 ?8 Z. R% ~) `

# q: I1 d# {' f+ P, xdrop table tmp;
create table tmp
(
$ E: x% a2 |3 x. @  I[id] [int] IDENTITY (1,1) NOT NULL,
* l" Z: n4 v+ b[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
  {6 o: {6 s, C' z);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
3 N& s* Y# Z2 w3 `- b+ qset @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)
, X3 {( ]% |$ V& c( O9 h9 Hset @depth=(select top 1 depth from tmp where name=@name)
, M1 x/ p& L. i5 G2 @3 ?* T0 [while @depth<>1
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)
# o' M. C) L* m1 rset @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