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

admin

查找文件的语句
, Y$ g+ v8 U$ A1 L  c1 D- N
CODE:

! b4 ]0 ^$ c1 t4 l( xdrop table tmp;
& g. _; {8 W& B& G  ocreate table tmp
(
, q$ E" b: u! `% e+ o( x[id] [int] IDENTITY (1,1) NOT NULL,
) K: t, j6 T2 H0 z1 {1 S/ x! b+ d[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
( }9 o) U: }" U- t$ ?) y5 |  A[isfile] [nvarchar] (50) NULL
! n$ j' n$ f0 k- d, O+ ?" I);
% _7 t- p7 v( _2 H- E* F
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
1 }, k" {# u; y1 N% v7 @insert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
, i$ u1 k7 `4 ?7 eset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
, u! }3 b! z# }7 L# p5 n& Jbegin
8 ]( L% b1 W" |7 G' aset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
$ U# o1 N2 V9 F( Sset @depth=(select depth from tmp where id=@id)
5 S+ _0 y* }0 B8 s# I) qset @name=(select name from tmp where id=@id)+'\'+@name
end
$ N  ~0 E% L1 O6 o' H6 _update tmp set name=@root+@name where id=1
select name from tmp where id=1
7 l8 ~$ _. W- J1 m9 q6 P3 y/ `" O2 f
0 b0 r$ C2 _. j& q查找目录的语句
9 D! a5 E6 c3 l8 }1 Y

CODE:
+ o& Q! t8 ?& I2 o8 H. t. ~& E
9 L+ K* R& q* [8 |2 L- D2 j
drop table tmp;
9 J- Q8 D: U" Z3 r4 W' Vcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
$ O$ x) V/ }4 |' U);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
0 P6 k+ M/ }- `: [- rset @name='donggeer' -- directory to find
  e7 J: V- v3 I1 j6 S, {. Xinsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
3 ?: f# p1 @2 D' o  X8 z4 iwhile @depth<>1
begin
. G, E; p) x# l% j. s+ _. r( Qset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
4 @& ]( x' p: D! T/ C: g8 Mset @depth=(select depth from tmp where id=@id)
4 t2 \) {) |  `% M- Eset @name=(select name from tmp where id=@id)+'\'+@name
8 S% l3 `' ~3 c% g2 F: m' bend update tmp set name=@root+@name where id=1
. a5 e6 e0 }4 F3 v4 r+ }' }7 Oselect name from tmp where id=1