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

admin

查找文件的语句
9 ^* X) u4 |. T9 X* ~* `# h, `/ I
CODE:
$ o0 V; g: E* u0 Q, w5 Y
drop table tmp;
create table tmp
(
3 {, m. t& D+ L, r; Q+ B[id] [int] IDENTITY (1,1) NOT NULL,
- @6 ]. ]- a! V. s' y- W[name] [nvarchar] (300) NOT NULL,
4 i0 r4 _6 M5 w2 h  I[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
% O. I! w# Y- X% ~4 k( e' F$ u);
7 o8 n' J! \& I+ ?% t9 x
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
1 h7 }; B6 Z- p# a! R9 y& Yinsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
6 R, x6 m# `- {+ H7 l% uset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
2 R- {5 {3 ~5 Gbegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
6 c, \# G0 r# g4 g' Qset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
end
/ i, k" z& A/ s, S0 F! \update tmp set name=@root+@name where id=1
select name from tmp where id=1
$ V1 ^* F- K9 v7 ^' u
查找目录的语句
3 [9 K: u: k$ @( `( O4 \, D! S. A

CODE:

! M+ J' m' u2 T- P4 N& N
) v  s& _0 K6 a' P8 s" j: F# w& ddrop table tmp;
6 \. \- y9 H; _/ i  Pcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
$ o2 R6 [: Y0 b' s" ?5 n);
( f( n' b# L. o
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
! ]" Z) o0 y! Y$ }5 wset @name='donggeer' -- directory to find
' p& B& p+ J3 v1 Einsert into tmp exec master..xp_dirtree @root,0,0
  \5 o6 C! }: E* n( qset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
* W. G! n* u$ {1 l" Cwhile @depth<>1
' y" y, |# e# T, M7 ~: Lbegin
$ b  ]8 H& ?; W, U- qset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
; G2 {/ m2 _4 {- yset @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
- D$ z7 ^) B: N9 Z0 uselect name from tmp where id=1