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

admin

查找文件的语句

1 I- T4 Q, F- f. p0 s' kCODE:

3 C$ O' I& I+ c' L- u% A& [drop table tmp;
# _, \$ k3 t) q" {create table tmp
(
) N+ P; R% \, d' Z, w[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
, b; R9 M7 B4 y& c' T. s[depth] [int] NOT NULL,
  e7 ?: S/ c8 Y( L+ J[isfile] [nvarchar] (50) NULL
9 x, ]' |/ X9 M' U! I. U, G7 a);

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--
; l, @/ p/ o5 K  C7 B* z0 {; Vset @id=(select top 1 id from tmp where isfile=1 and name=@name)
$ d/ P! I% ^* G0 f3 u( R  W$ Uset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
$ V6 V" n- F. S3 Jbegin
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)
" n! o! t/ e  kset @name=(select name from tmp where id=@id)+'\'+@name
# m2 C$ ?! n. e8 iend
update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
3 F* u, c5 Q/ |3 R4 j  M

CODE:
# V% z2 A& R1 ?0 U/ ]

) l. j' d$ J+ d+ x: Vdrop table tmp;
create table tmp
(
: T) V0 R3 y* ~1 E/ e2 j- _[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
6 c5 g0 Y# b0 n, W# X[depth] [int] NOT NULL
);
$ d$ X; g' W* v5 v$ l, w% I
" Y) Z0 d5 o: N" _& x: gdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
7 W' `7 X$ l  E2 ~4 [- Sset @root='f:\usr\' -- Start root
& X$ D* y! Z: I4 I9 D3 P: mset @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)
5 |1 \7 c; f) _' i0 m3 t% fset @depth=(select top 1 depth from tmp where name=@name)
9 P8 t* o5 Z0 C! `: Xwhile @depth<>1
  m/ t- {& {" ?" F. cbegin
& L, r- q+ k' v6 Cset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
! v- `4 _* {# @6 G( ~set @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
5 Z( K3 h, r0 C4 m4 K) e9 Fend update tmp set name=@root+@name where id=1
# A1 p/ |) r6 ~* Q; t5 J& u2 l) g  }+ \select name from tmp where id=1