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

admin

查找文件的语句
' h& S  i* R/ C5 g/ A! h  D
- Q* }4 w" \2 iCODE:
9 ?( E; T7 y* l7 V% ~
drop table tmp;
create table tmp
6 n7 [( j' v7 |+ m( P(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
* G3 ]( i1 \& A: a[isfile] [nvarchar] (50) NULL
; T, E) N4 ]8 D: S);
, k) K8 h+ N: F6 d, x+ M
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
% P0 q. }" u+ N& qset @name='cmd.exe'   -- Find file
+ Q$ ?% v: v2 P( @* e- f. Pinsert into tmp exec master..xp_dirtree @root,0,1--
' D1 ~* Q7 n! ~* H1 Yset @id=(select top 1 id from tmp where isfile=1 and name=@name)
/ p4 W; f2 g" h5 |, mset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
begin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
9 L2 J/ S+ {4 u2 H6 e, ?, \set @depth=(select depth from tmp where id=@id)
: ]! A) E" x; fset @name=(select name from tmp where id=@id)+'\'+@name
end
' @8 o3 N/ o) C& F- _update tmp set name=@root+@name where id=1
3 h) T$ e, b1 {' b/ L: Y; e) uselect name from tmp where id=1

查找目录的语句

3 ~: `; f* M: \! S5 p: [: o0 E& C
CODE:
# S+ {: D, s$ v& I

drop table tmp;
create table tmp
(
2 b; t3 ?( H1 z9 ^[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
+ {! T& }# Q9 J1 w0 x% rset @root='f:\usr\' -- Start root
% f) k( ^0 A# m2 Mset @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
( Q/ |( O' V! V& uset @id=(select top 1 id from tmp where name=@name)
, [8 E2 J5 s6 u1 q; nset @depth=(select top 1 depth from tmp where name=@name)
6 p4 q  v3 s3 I% j& H; Y# D) |7 Z5 k! }while @depth<>1
3 l% z" e+ ^8 X) e. c/ ?1 Qbegin
set @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)
; }6 w' J/ ~$ k; n* D9 b, cset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
# M8 |: z  f) {! D! L; N5 K" f. _4 B0 Eselect name from tmp where id=1