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

admin

查找文件的语句

# r: z, U- k7 sCODE:
  W* M9 h' y" N: O) F
# T6 s# U4 D$ j( r' ddrop table tmp;
create table tmp
0 C4 C1 l% L  v$ C(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
( Z( ]3 Z* {" F; N; V[depth] [int] NOT NULL,
* i. f$ J3 ~$ l6 C: a8 s9 ~; ][isfile] [nvarchar] (50) NULL
);

3 d$ |5 f) f: vdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
  E; F! P; M5 Dinsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
# {+ F. E4 a4 n. D7 F( C9 R; c0 Vwhile @depth<>1
) M/ S0 y( G' Y7 n, qbegin
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)
set @name=(select name from tmp where id=@id)+'\'+@name
end
6 F, y$ f. C. Hupdate tmp set name=@root+@name where id=1
# P- q2 f; Q8 A  fselect name from tmp where id=1

查找目录的语句
8 M. x2 ~; I- l

# z- T1 n* H: y# A/ ^CODE:
2 S, [5 `7 I# q& @

/ q& Z* `6 k0 P; j, A/ w- |' [) ndrop table tmp;
' I* H3 O0 ?- p$ Wcreate table tmp
2 e8 J% r$ S6 s6 t7 ^3 ^(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
  S3 S6 S7 u& B( t1 P3 p
/ w3 s* v% \* `& s2 _3 I+ p: Vdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
+ T/ T" `; ]/ w+ y  ?insert into tmp exec master..xp_dirtree @root,0,0
8 ^' z: x$ |0 P5 n" mset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
4 [$ }9 V8 s, D4 lwhile @depth<>1
: m, N% w& O6 Ibegin
2 b* J- z' V8 M) z6 d: b. `set @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)
* v" k" s# S+ t0 l! }$ \0 g% iset @name=(select name from tmp where id=@id)+'\'+@name
: }/ P% d6 g* ]4 S/ z/ a+ rend update tmp set name=@root+@name where id=1
select name from tmp where id=1