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

admin

查找文件的语句
) F+ \9 s0 W2 u3 u
CODE:

3 q9 G. O, w* [6 d5 ]1 B( Y0 xdrop table tmp;
create table tmp
# k) y. ~+ z2 F1 t, `, r$ d  `& _" ?(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
( E# l0 g0 @8 V
( r7 b, }; i# S$ t6 c" Qdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
) y! {( @( ~; z1 G' ^" ?set @name='cmd.exe'   -- Find file
8 `- V3 B: {! y  ]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
8 X- j* g' t4 Y5 [' F- t. Y1 }begin
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)
& v. w( t" D# J+ e' r' pset @name=(select name from tmp where id=@id)+'\'+@name
$ F; x5 j( R! x, S/ g6 d9 L5 hend
update tmp set name=@root+@name where id=1
$ O" O" \+ X' o4 Tselect name from tmp where id=1

查找目录的语句

* M9 p1 p( X+ j' F3 l
' Q3 C% y" J% U- e8 QCODE:

2 R: u/ `, ]4 p3 t
drop table tmp;
create table tmp
$ f: q- ]3 i: k) x0 N/ Z(
" x9 T' F0 ]/ I9 L8 D[id] [int] IDENTITY (1,1) NOT NULL,
! r( S* A4 n! o" w4 z* E[name] [nvarchar] (300) NOT NULL,
! @! E# k/ s' }/ N, [, [. C[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
2 r2 X; ]% ^- s8 ^set @root='f:\usr\' -- Start root
7 x3 v4 h7 Q5 k2 V' u+ `+ xset @name='donggeer' -- directory to find
+ @6 J, L/ W( b7 k+ ainsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
  v) u: P3 l  H4 tset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
  W) Y2 E! c% E6 b1 D! `6 g) vset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
4 C, F5 a8 b! W( `( O+ q2 Nend update tmp set name=@root+@name where id=1
; G- _; l6 X4 S0 Vselect name from tmp where id=1