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

admin

查找文件的语句
" C/ I, w* F; ^, @; e! f' c9 M
CODE:
5 n/ M" N! n$ Z
drop table tmp;
) x( v! b9 h  n8 mcreate table tmp
# F- s6 V. }# y2 N( R# l(
[id] [int] IDENTITY (1,1) NOT NULL,
: ?2 d# H8 X5 X5 r! F$ e! g[name] [nvarchar] (300) NOT NULL,
2 @2 b" M; p9 z& x5 C8 Y" L[depth] [int] NOT NULL,
) q: w4 j+ i* a' `% d3 {7 N[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
# E+ a3 J  N9 d4 Oset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
, I- |$ Z% F* I: G. ?, dinsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
9 x0 t4 q) q6 G5 H6 p! g8 v3 V, Rset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
$ t4 O) e' E. ~; t+ K& nwhile @depth<>1
begin
- H, B/ f! d4 k+ S; W) lset @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)
; [; z% Y8 S3 O0 E$ _! R, f4 ~set @name=(select name from tmp where id=@id)+'\'+@name
end
, b+ w( Y" j4 q! h2 jupdate tmp set name=@root+@name where id=1
+ E# g2 X- s+ h8 Rselect name from tmp where id=1

, r  b7 v0 Z3 o# }- a# D/ J查找目录的语句
5 E; K; o9 i9 j3 p0 f& S
& ~+ l% F) F% F  ~
CODE:


drop table tmp;
create table tmp
(
# I" i$ F! V+ N* }& {[id] [int] IDENTITY (1,1) NOT NULL,
3 Y0 t0 Z9 W! K' J1 i[name] [nvarchar] (300) NOT NULL,
% Z! T- f4 y' E  P[depth] [int] NOT NULL
);
0 n" D& ~5 V& E$ H# u) t
# q( ^  r1 b, x4 x' Cdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
5 F% t' R3 _8 o7 r" _1 J2 \% iset @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)
) q9 x" e" l' z! Ewhile @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
& A7 u: E( ]2 tset @depth=(select depth from tmp where id=@id)
; u) L) _% y. o% d4 A) cset @name=(select name from tmp where id=@id)+'\'+@name
1 X9 v% }1 d" {# D& x1 V2 f  G& a5 S" ?end update tmp set name=@root+@name where id=1
select name from tmp where id=1