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

admin

查找文件的语句
1 R+ v9 U+ [% z6 s, c
CODE:
: |. r5 A: N/ o$ `
drop table tmp;
create table tmp
(
3 E' k& d& S0 K0 T7 g$ E. \[id] [int] IDENTITY (1,1) NOT NULL,
: ?2 z5 {, }- w6 f9 @- E! v6 h[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
7 n1 u8 J: ^) `/ |[isfile] [nvarchar] (50) NULL
7 P- k8 Q6 `6 o8 N( U; P( z: P  y);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
/ Z* D' J$ K8 t5 H5 Nset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
% `0 ?/ q* ^; x/ d" @3 oset @id=(select top 1 id from tmp where isfile=1 and name=@name)
- k2 A. S! o+ N+ @2 j3 N7 w& f3 Hset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
- N, p0 `. R2 N* B+ Jwhile @depth<>1
begin
& W9 F# j1 G: o0 J2 |% Aset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
( K, q) i- {( K' `set @depth=(select depth from tmp where id=@id)
3 ~5 Z1 y6 D  P1 ?( {set @name=(select name from tmp where id=@id)+'\'+@name
end
" X' ?- Z# i# P) t6 K+ bupdate tmp set name=@root+@name where id=1
select name from tmp where id=1
5 t( z. f' {- U' T, M
! U2 l1 {( J3 h  r' E; b查找目录的语句


CODE:
$ e1 }, h# D, Q) _. ]

drop table tmp;
create table tmp
(
" }: U' C: ~; l2 p$ v1 V' S[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
+ h0 a" ^# U& W, F+ f( l& ?  P[depth] [int] NOT NULL
9 s' k6 x- w' o0 ^) s);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
& e2 e% A( ^. e* s( rset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
4 X/ b0 B: d3 |/ r1 Pinsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
) _$ ?, R% P0 ^' ^5 f8 M' a6 ]set @depth=(select top 1 depth from tmp where name=@name)
5 m* u4 b7 M+ \5 e/ zwhile @depth<>1
begin
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)
set @name=(select name from tmp where id=@id)+'\'+@name
! w4 j$ [9 \8 D+ K* }end update tmp set name=@root+@name where id=1
select name from tmp where id=1