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

admin

查找文件的语句

& z4 j" F' u1 a& n% O; yCODE:

drop table tmp;
" j3 q; r$ Q$ w' Acreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
: i0 \# ~/ t- z1 g. h" r+ U! {5 y3 w[depth] [int] NOT NULL,
+ A- }& c# O$ G2 o, z0 ~[isfile] [nvarchar] (50) NULL
);
9 j. o7 u. @9 G! I! X' [7 N
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
: W+ |- k) E9 K+ }  C0 {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)
+ n2 k0 w7 C: uset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
% J7 l# u7 v7 t( i0 g" L2 qwhile @depth<>1
  x0 Y" ?& i+ d" a! B3 I3 P$ cbegin
& _; v& j& {7 d2 Q) B. ]* e  k9 Hset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
( s4 ?: ^+ ~9 Y, j0 N+ h# |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

4 |( X  O  [2 q* e查找目录的语句

% h) A% z( H& O: h) |
CODE:
3 m6 z8 s# W; p/ w) K6 n- p

drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
# o; ^8 V$ J% X) d( Z[depth] [int] NOT NULL
' s- X; X$ l( ~. |);
/ V" r7 ~& r" Y" [6 r5 ]1 t
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
4 Q, n  u- o* s8 w2 Zset @root='f:\usr\' -- Start root
6 P( E9 N* B3 U! X* qset @name='donggeer' -- directory to find
$ d2 H9 S6 K/ l' {# z6 O/ Tinsert into tmp exec master..xp_dirtree @root,0,0
2 X6 D" h0 j' R, \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
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)
7 @+ Z% B! ?: ~5 dset @name=(select name from tmp where id=@id)+'\'+@name
% J6 @6 X8 f" M: Eend update tmp set name=@root+@name where id=1
' L0 O. a& `) }" b5 s% }" p" Iselect name from tmp where id=1