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

admin

查找文件的语句

& X6 }# E/ D) X3 `7 ~) h8 v/ ECODE:
5 a9 N5 m. N  n
drop table tmp;
1 i! G7 r+ j. T4 o2 bcreate table tmp
( g3 P; R; F+ W& I& a(
[id] [int] IDENTITY (1,1) NOT NULL,
  B; ~' K$ Q1 A. Q[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
- ^2 P" a4 d+ g[isfile] [nvarchar] (50) NULL
7 A9 |) V9 b& o2 q2 \8 ^8 v);
* @# a- e5 ^+ d9 x$ u
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
% U! e% G* d7 C: a+ K6 P+ A8 T& h# _9 Rset @root='f:\usr\' -- Start root
- z7 r; H2 D& U2 kset @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)
! L0 D6 u- ~5 rset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
; Q+ s. \. L5 \; Mwhile @depth<>1
9 F3 s' u( ~7 S3 V  r  @- `' N) Qbegin
- }5 n/ N3 B3 r% q8 [/ p: t  u) nset @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)
set @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
' `# c2 r8 q. `- S7 `" L7 I4 A+ y& N. Zselect name from tmp where id=1

查找目录的语句

; U. u: N7 e% N. y& Y$ t9 s
CODE:


$ r, i9 |+ }% E2 Gdrop table tmp;
create table tmp
5 t+ B: u* y$ I$ c% \8 H1 {' J6 r(
0 P. d: z3 K8 {/ u! f[id] [int] IDENTITY (1,1) NOT NULL,
. p9 x7 T4 |0 l[name] [nvarchar] (300) NOT NULL,
; b6 h0 @! N1 w- R& Z[depth] [int] NOT NULL
5 {7 k- F. ^' P. U( A);
% l( n: l4 V% j( l
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
4 T) a+ ~* g( c+ s" D' l' B4 X% Z. w+ _) iset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
! F- f! L, s! p0 A! t) Rinsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
- }' B4 V6 k. |8 bset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
8 O; l, ^: [5 q4 I" Q+ i  X- Vend update tmp set name=@root+@name where id=1
% z  z, @/ ?7 Q$ Kselect name from tmp where id=1