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

admin

查找文件的语句
' w3 T5 N0 ?5 ^; m& x
CODE:

8 ?) y2 `. Q  ^7 w; G+ B3 U* ?drop table tmp;
$ ]2 H, e% \% {! W5 M/ Ccreate table tmp
(
  {3 e% L5 E. L& w% x3 n[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

8 L8 u+ Z9 l* vdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
3 r2 W2 {9 p8 nset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
# H% s6 x3 L' s; n, V- L; a0 N0 ?( winsert into tmp exec master..xp_dirtree @root,0,1--
9 J- i4 ]7 K7 a: X4 Jset @id=(select top 1 id from tmp where isfile=1 and name=@name)
& |1 a3 D( ^# [5 R% g$ rset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
begin
: H. Q5 z, b( |4 yset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
, X/ A" y2 n. Z$ a+ G& Qset @depth=(select depth from tmp where id=@id)
9 L! s, c6 D! H$ [set @name=(select name from tmp where id=@id)+'\'+@name
6 X* U. C$ c7 q) p9 Eend
$ |$ i7 l1 L8 D5 Q/ G/ Kupdate tmp set name=@root+@name where id=1
3 P+ r8 x) V- D( a! q$ R) D& `; cselect name from tmp where id=1
2 o  {7 [; P& I; o  D9 j
查找目录的语句


7 V: ?, M1 H' d1 x- D; i5 xCODE:


drop table tmp;
, M: ]( j3 r; O- N; p( H. wcreate table tmp
; J5 U' q& d) Y  D& E( R* `& c: W(
. W' G2 G- w' |% j7 l( n' B6 m. \[id] [int] IDENTITY (1,1) NOT NULL,
- d; i  D  [8 G8 m[name] [nvarchar] (300) NOT NULL,
6 O+ U) }, y8 K$ J2 x) U1 b/ {[depth] [int] NOT NULL
1 A# a$ W, b* W7 v( Y! T3 m);
( W) W# _% p6 ^) O6 u
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
) z$ T( b. C9 f7 xset @root='f:\usr\' -- Start root
$ E- ?+ \3 N9 K5 N+ K( m+ W9 |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)
5 k9 Y6 T9 O0 d% H3 \- ?0 rset @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)
5 H' g) E3 W/ E+ ^set @depth=(select depth from tmp where id=@id)
; L5 R0 ~7 S2 T9 wset @name=(select name from tmp where id=@id)+'\'+@name
2 Y; K, g! l% ^' k- U: T( Dend update tmp set name=@root+@name where id=1
, X+ }0 V( Y7 }, uselect name from tmp where id=1