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

admin

查找文件的语句

CODE:

5 A2 H8 c" L+ q, i- |drop table tmp;
" K/ y3 B* ?# T1 Z  A) ycreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
' H: t# ^3 J" r0 Z' D# P[name] [nvarchar] (300) NOT NULL,
' d! d. o+ f, e7 G; R1 X6 M1 }[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
* I# X4 g: X8 W+ g- N);
6 }& l7 }8 s! W. t' t* \# Q
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
+ D6 q9 q  j" J  p7 L2 c5 Zset @name='cmd.exe'   -- Find file
; c* I9 n$ v, r7 s; Oinsert into tmp exec master..xp_dirtree @root,0,1--
7 }# J8 s! [" K- n# T1 t8 qset @id=(select top 1 id from tmp where isfile=1 and name=@name)
5 u- [1 E/ G% V$ @% q9 B' B7 oset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
% t. ~( W3 B# c3 Z, g* B' l3 l8 _while @depth<>1
begin
( d6 R! z( h( h2 q6 Yset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
, \9 a  d0 z) V4 X' cset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
select name from tmp where id=1
+ Q$ t2 s" Z3 J6 I
  B( |2 M3 u. i$ J# }0 J% L  i查找目录的语句
( \) b. N) Y+ ~, J# L- p

CODE:


8 @$ O  C. H% M/ `6 l0 Mdrop table tmp;
create table tmp
) @' S: m; v* j6 M& N8 ^$ U(
) g# Y- W+ e* y. G! A[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
$ i! v1 l! Y* x5 C' o[depth] [int] NOT NULL
& k: q8 W+ l0 @- V- o! M* ~+ c  E3 s);
, C3 W* i5 }. c4 x# t
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
5 t- r% x8 ^) mset @name='donggeer' -- directory to find
2 r+ H1 Q+ z9 O0 G& l& g; i8 I- binsert into tmp exec master..xp_dirtree @root,0,0
- n3 H# _6 l( ]" ^. A. K( \set @id=(select top 1 id from tmp where name=@name)
4 ]) W3 l3 p, {1 B7 U  @. A! O+ Pset @depth=(select top 1 depth from tmp where name=@name)
% h. k- d1 C8 X! m8 P2 Qwhile @depth<>1
, v! U- z1 L4 W  |begin
* M+ V* k. _& l. c1 E% X, \- Z/ nset @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)
# o' Z% c& C% X+ O, Mset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
* R) G$ a4 K5 F; z. D! z- n$ nselect name from tmp where id=1