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

admin

查找文件的语句

& G; w. q* n1 U- w+ A( f# ~CODE:

5 C# ?) p; d. G. odrop table tmp;
: R# I/ r  n4 Y3 pcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
% f' b$ g3 L# N& `[name] [nvarchar] (300) NOT NULL,
% k6 P8 Y( A2 x* J" H[depth] [int] NOT NULL,
% Y, S, \! K/ e! U; x( O5 W* E[isfile] [nvarchar] (50) NULL
6 I! c0 [+ C3 p: N: _);
+ S- C* S( |4 [0 C
0 t( K- b- M( p" f: zdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
9 t/ H  C5 R* C9 D7 e& |# Uset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
% c) F$ K. C4 w$ Oinsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
& f3 V8 W8 ]+ l) g2 vset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
- k: ]( B& B1 n, L, [while @depth<>1
& v/ T! o! }) A2 `% t+ j2 i$ Tbegin
set @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)
) x/ }8 Q& c& I, {, r7 A, aset @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
2 ?- a" H$ a' Qselect name from tmp where id=1
2 e, h; m, K2 L; b
  M1 j5 }" V3 V& ?3 c/ C0 j  {/ O查找目录的语句
4 Z/ H! U* ?% U
7 o7 f* Y7 n1 O: P; H4 M- {# D
CODE:
; w4 n! Q% Z$ a6 n0 x; q  w( }( d
/ L' [9 _. H# p" O. y/ g/ _
drop table tmp;
1 L6 {4 z" y9 k' x; \! j: L- ?create table tmp
(
5 [1 g! m( J' a, s, Y[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
& q7 b! H1 o4 b$ q
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
+ D; b/ l- T% Wset @name='donggeer' -- directory to find
5 e7 Q5 R+ v0 \) A9 u" @4 K( k2 Pinsert into tmp exec master..xp_dirtree @root,0,0
7 ?1 A) x8 L! n9 l, z4 Gset @id=(select top 1 id from tmp where name=@name)
# f! |( X5 R" Z3 _set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
6 v  f/ I4 v( \  Ybegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
1 l8 q1 K" x9 ~4 [set @depth=(select depth from tmp where id=@id)
6 F5 w3 |  z. x' Q, r! ^/ lset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
+ [5 k# C" E. g$ Zselect name from tmp where id=1