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

admin

查找文件的语句

7 `& `& W/ g$ @/ J9 U6 Z" r8 VCODE:

: F1 Q2 q# O. G% Mdrop table tmp;
; ]5 O2 o  U) Icreate table tmp
. w+ O* n9 y0 k4 g  {( P(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
$ H/ y/ ?* Z3 ^2 a9 ], j[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
& `- [4 _% _7 W);
2 Q% R0 l8 }! _$ [
- w; r7 l/ h# J! V7 Q# R8 Adeclare @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
' ]" n  F# n0 zbegin
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)
set @name=(select name from tmp where id=@id)+'\'+@name
end
# U6 A) g  H5 a5 Z' M% \; rupdate tmp set name=@root+@name where id=1
select name from tmp where id=1
8 Q3 Q; U4 i( u" H
查找目录的语句
1 x( Z5 |9 R/ U0 x& B! y! c. V2 W: g

CODE:
$ f0 o/ C  K0 d* m# b' e9 W

drop table tmp;
$ X' I: W* [% r, Z0 _, Icreate table tmp
% _, _! N' A8 v4 B! u: N) B1 u, H(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
7 _: j) T4 ~! a3 S- x);

3 Y9 H0 ^+ K+ u4 Q( k) Ideclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
' V6 T! l1 b  |- }) h$ X  Z1 m5 iinsert into tmp exec master..xp_dirtree @root,0,0
; @+ N+ E) x* K- zset @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
9 H' m; _  A& ]' N, N! H2 dset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
& L$ S0 `, t' Yset @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