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

admin

查找文件的语句
- c% g, t* w6 p6 Z# L
" b0 G3 x6 h& WCODE:
, V# }# P# N8 K
drop table tmp;
6 u$ h# i# V! E  l$ [& @4 K# Y* P* screate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
2 G, G  B9 Y2 c) Q- _. w9 B/ ~1 ~[name] [nvarchar] (300) NOT NULL,
  D. I: E5 q$ c  t" G[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
) H; H7 ?( x1 ?; m$ G% c+ g& @7 z);

" a6 \" E8 q0 v) i- r9 [+ Vdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
3 k3 c. V% R! w# l  Uset @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)
5 _; R# ]- `4 P/ [& F1 Fset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
begin
9 T; q% B! z) b5 {4 }set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
/ B7 ^4 v* l! B4 Fset @depth=(select depth from tmp where id=@id)
0 u& T3 ?! A  q! M! U) G! x; Nset @name=(select name from tmp where id=@id)+'\'+@name
5 ~7 @5 u8 H9 h# _2 @. H% q* Dend
update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句

) R& u% m3 _8 a8 a, i/ f
CODE:

7 O2 x; c) t9 a1 m8 x# C
0 h( o5 |5 D. t7 Y$ u+ vdrop table tmp;
$ S1 j7 Q6 `& z, B: F. r6 ncreate table tmp
' T1 p  v% `$ [# d3 }(
[id] [int] IDENTITY (1,1) NOT NULL,
* J( w% \( J8 [, E3 W# R[name] [nvarchar] (300) NOT NULL,
8 F! j9 Q+ M3 G5 \* Z% \- E[depth] [int] NOT NULL
);

% ]! {- d* w! S* }' xdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
5 B, B9 b' W- a( iset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
0 |4 r6 v0 C  B0 U' T3 D/ G6 einsert into tmp exec master..xp_dirtree @root,0,0
# e2 B) l' R9 T" nset @id=(select top 1 id from tmp where name=@name)
- h1 u: v- |3 Y5 I" H: L+ k- s+ Dset @depth=(select top 1 depth from tmp where name=@name)
# P0 ?6 W$ f2 Y, kwhile @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
& \6 ~% H: X/ Y9 Gset @depth=(select depth from tmp where id=@id)
% h( q" Z- g2 N8 J+ r0 ~set @name=(select name from tmp where id=@id)+'\'+@name
) C$ C" U3 |. X# \$ S* Y, m0 @end update tmp set name=@root+@name where id=1
( K' D" w9 |2 f6 N6 eselect name from tmp where id=1