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

admin

查找文件的语句
% C6 m" N: R, ~
$ q1 c# ^5 f8 G" QCODE:
9 E! L: v: Z; N1 j; t6 `
/ b1 c% d; q' C$ H( I$ d4 wdrop table tmp;
create table tmp
4 o% P& ?" ?* R, M9 d0 l' ?(
: t# ^: }8 I% t# M% t[id] [int] IDENTITY (1,1) NOT NULL,
$ N: e$ X, V% n" p+ U6 t7 ?  T" i- X- W[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
& i. ^0 N) I# j: s: m[isfile] [nvarchar] (50) NULL
3 C, _9 y1 g' V( l$ T& J1 O);

' _0 X! {( S$ [. ddeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
% f: {  z6 i) X- I( x0 O& Lset @name='cmd.exe'   -- Find file
- I; \& W) r5 N( A- j* ?. ainsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
' S# u1 X1 x/ [9 J- hset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
9 t0 \+ @% `& P8 rwhile @depth<>1
begin
+ Y' {% ^" j% A8 u# Kset @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)
" E3 k7 c( G9 j+ Kset @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

查找目录的语句


CODE:
5 d/ J+ U1 u1 J& g6 Q
8 |+ V% Q. Q! G' S! V/ e% Q! @+ ?
- E( E7 P3 x% t' S  v8 W! V7 odrop table tmp;
! t4 T7 u* o# ~. B6 ccreate table tmp
(
7 d- ]" X0 Z) X+ \; E[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
+ r6 a, {. ~4 _0 F9 Q1 V);
0 r5 w7 j* `" J
  _; c( {/ s6 p, i( _+ x6 {7 Tdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
" u3 [( n: k/ S$ v0 Bset @root='f:\usr\' -- Start root
" ~9 h7 _7 Z% X9 d" P) fset @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
! {% d* s8 e, k- x( mset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
/ O+ s; G9 Q8 A/ H4 Mwhile @depth<>1
/ a9 u2 l0 _: V( Hbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
3 t+ D5 p$ {  u2 Tset @depth=(select depth from tmp where id=@id)
; X& c- L+ O; a& B) |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