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

admin

查找文件的语句

1 Z7 u* o+ v, `+ CCODE:

# `, m8 D# s& p4 K) }drop table tmp;
7 g0 _6 P3 c( s' D1 V4 k6 ycreate table tmp
+ Q1 q, e4 }& @' Q5 ]- H(
! N# ], I1 k- Z/ x& [[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
" B5 e7 I5 Z- A" r; [3 \- \[depth] [int] NOT NULL,
4 v/ _* K$ c0 {. x7 ][isfile] [nvarchar] (50) NULL
) E* J9 P  D* q  _5 `);
. P" E" |. _6 X
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--
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)
, v' [+ x; N/ W- A  U# v( E$ u- zwhile @depth<>1
' o" v& \) L: S+ L1 E8 X$ E4 J; Kbegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
! R# [. m6 }# k) S7 p* E( ?set @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
' `/ o6 z- D5 q1 r0 u  A1 s" Dselect name from tmp where id=1

" q( d0 `" W5 y' {& N查找目录的语句
! D" l6 t  p7 [$ J4 \% s, R

CODE:
3 J( p6 [1 V/ l1 ?( y* x
- t1 X/ n8 h$ V
1 l$ `2 H& e. O1 fdrop table tmp;
# d9 p& a$ E" P. R+ Ucreate table tmp
(
1 t9 I' B. a0 }8 S+ d, z6 i[id] [int] IDENTITY (1,1) NOT NULL,
5 ~2 t; T5 e/ O& |% D[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
1 d) @0 m* r7 X+ Q4 M! R);

! ]. w# Z$ O. [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)
set @depth=(select top 1 depth from tmp where name=@name)
* T5 Q) w& `9 U0 j6 Q! Pwhile @depth<>1
begin
3 r# t& v+ r! `) u) jset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
0 U, W) g4 ?! ~' kset @depth=(select depth from tmp where id=@id)
7 [& s& @- J  J: h" B$ b  ?8 Yset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
" g6 P: J9 l$ |0 f0 V' ]" Lselect name from tmp where id=1