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

admin

查找文件的语句
# [+ k5 q0 p" X8 }6 s& `
+ [" C! R8 g" `) u* G) XCODE:

drop table tmp;
1 G2 D' b& Z6 k; ecreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
* c6 m$ l3 \6 ]4 |[depth] [int] NOT NULL,
7 s2 d5 [1 F" y& i( R4 r, R[isfile] [nvarchar] (50) NULL
);
+ {- Z. ]1 Q. C! D* {5 h% l9 @
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
8 P4 T" u. M3 W  n- _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)
9 ?( Z9 j6 R8 ?6 fset @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)
  W% Y$ z# ?6 b+ Vset @depth=(select depth from tmp where id=@id)
$ p, {& _" M& ~! ]5 Y+ sset @name=(select name from tmp where id=@id)+'\'+@name
end
0 C  w; Q! ?& Q( i8 J  Mupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
. D* e4 t6 k4 v; r4 B
9 T+ T6 x) r1 p5 x
CODE:

5 [( G6 k/ z9 e; w; e5 Q
) R$ i+ b6 p- N4 rdrop table tmp;
create table tmp
(
. v4 d0 k! j% p4 d  ~+ l3 f- d& r[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
; \9 w5 G( z  D3 j; i$ y[depth] [int] NOT NULL
' y6 ?2 J6 T" K8 n$ u);

- l+ b# ]9 x% v6 P) l- Wdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
8 X7 ]% V" e' Zinsert into tmp exec master..xp_dirtree @root,0,0
- z5 ^( Q8 p/ g: }% Tset @id=(select top 1 id from tmp where name=@name)
; |, m# k' l9 o! ^; Nset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
6 i" t% ]% c  M) Ibegin
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