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

admin

查找文件的语句
; N. y( `# U# Y9 T& v
) S0 ]6 b5 e3 H9 y" T; RCODE:

drop table tmp;
create table tmp
; x3 E1 _+ }6 d8 z: u0 b7 V! y(
[id] [int] IDENTITY (1,1) NOT NULL,
  o* n/ f3 E9 L6 q" s[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
! s- y- T6 r; A/ E);
; e% a* r; u2 B& p& X# M( U
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)
% {9 s& l* L" F' l, _9 Lset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
/ m* J1 ^. E* s+ t1 `8 Rwhile @depth<>1
  p' n' v! k) z. ^8 T( I. p( obegin
$ ^1 |0 V2 p" v9 j9 ?set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
' p% A  p8 j/ F0 J* r( Sset @depth=(select depth from tmp where id=@id)
; f- X0 B7 ~/ M2 j+ Cset @name=(select name from tmp where id=@id)+'\'+@name
end
% k2 N( Q5 ?  A! P; Zupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
$ u; B+ ]! d8 F% y' O9 u: g

. N0 F! l  J8 [' Y; c& OCODE:

  c; O$ ?. Q; z+ [; H& K) Q
6 g$ b3 i7 J. c+ k2 q. i! [1 F/ Odrop table tmp;
create table tmp
% \( M4 e  `: \$ y" c(
- \% s1 @" s! g  O" g6 k. ^! r[id] [int] IDENTITY (1,1) NOT NULL,
+ e( z: v3 s  q+ V9 g% y: K[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
  T+ @( L, u. w* mset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
  f! L/ r3 n% t' e* c; h! Vinsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
. j% `" K1 B# e& m! C, Pset @depth=(select top 1 depth from tmp where name=@name)
& _" u' a# L+ u4 u0 R0 |# U/ swhile @depth<>1
$ K# b& ?  f7 ], K3 Ubegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
  n! O2 F4 w4 F2 P9 R. A7 x5 sset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
- g+ ?) }& f# g% d2 P& _. Pend update tmp set name=@root+@name where id=1
select name from tmp where id=1