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

admin

查找文件的语句

. K$ X7 y6 I) Y/ w% F1 jCODE:

drop table tmp;
create table tmp
- q, h- ^( s2 D, [  I* `(
, a/ H3 [7 T* T/ S: l+ f[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
) a) o6 O3 [" [0 T[depth] [int] NOT NULL,
1 I, q4 ]7 X0 b# x+ N1 v  o% q[isfile] [nvarchar] (50) NULL
4 E7 {$ b' _6 g+ D$ E);
7 _: p% ~9 U8 B8 A3 i9 d7 C! c
4 p$ u4 u2 j# _- ]7 b* l+ y8 Bdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
# L$ M2 S+ C0 @set @root='f:\usr\' -- Start root
# w- t6 i+ O  G; Z7 A) }8 \. V2 aset @name='cmd.exe'   -- Find file
, i8 s, Y/ ~/ o& _' @insert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
5 }& P0 s' f1 V/ J: i$ fset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
  v5 k. ^) ^$ e8 Z! {/ i' c( Mbegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
; z9 P6 O9 p* d& \5 N. d3 x7 e$ Iset @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
% C1 L. M0 g" [& d9 L, Pselect name from tmp where id=1

查找目录的语句
+ D8 U# x, B5 @: T0 @

6 T1 ^& d( u8 G) l/ KCODE:

7 w' p. D  c$ E( P: _; Z$ n2 m+ U8 s% |
drop table tmp;
! Z6 ~5 C" B# F9 q3 w+ |$ z9 Ecreate table tmp
. {! m% r* `& a' ~8 z8 F(
2 U/ h8 J+ s* {9 ?" j0 x3 L[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
6 J6 }2 X2 z  u[depth] [int] NOT NULL
$ Z4 ?' B/ {, K& P  _);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
; A0 D, T: T) y. B# g" g5 Cset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
$ W( i1 o) p2 pbegin
6 l6 U% W# O- Q+ [8 h8 _" uset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
set @depth=(select depth from tmp where id=@id)
0 ~- Z6 X5 R+ G9 zset @name=(select name from tmp where id=@id)+'\'+@name
  e& `7 y0 y/ y/ k0 L, G; H0 L$ T7 Aend update tmp set name=@root+@name where id=1
; R% c& V# R# wselect name from tmp where id=1