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

admin

查找文件的语句

CODE:

drop table tmp;
create table tmp
+ B  p: \  @5 k, B, l4 U(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
; E4 i0 r! z' w- O[isfile] [nvarchar] (50) NULL
);
( P% b$ r/ n4 m. [! q. p
* q0 X6 D3 \, l/ N/ W2 e4 t$ qdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
5 Y! r1 m" Z* n! P' k. p9 s2 yset @root='f:\usr\' -- Start root
set @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)
set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
2 E  z! q, b+ C: `+ D3 c) Jbegin
2 w2 y  a, i- H5 K; C3 Xset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
; A8 z: C+ n* c) Kset @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
$ \0 {2 C  [  ~0 r3 u& g9 Nselect name from tmp where id=1

查找目录的语句

* J/ V* x. P$ r( Q
CODE:
$ a$ p' J: o% ~$ T7 f& f
& f8 f' Y4 ]/ K8 T5 [
' ^4 b! V5 u# t0 X: J5 V' k5 pdrop table tmp;
5 h( U+ {' y$ mcreate table tmp
(
4 e, h9 S# |! E6 D& A* l/ |[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

' F& d! \9 q+ B! Rdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
; Y( I1 A9 p0 ~; Dset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
5 }  F! ]4 M# d3 Uinsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
5 s! E. K; H/ u; t. ]& `set @depth=(select top 1 depth from tmp where name=@name)
; \; i3 r% K" G+ H, X8 `3 g" pwhile @depth<>1
6 `2 m2 V( i' L; I) Cbegin
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)
  g4 ?1 z* A. p5 u/ {. b+ L: Cset @name=(select name from tmp where id=@id)+'\'+@name
) x! w7 e: y1 ~4 o, W/ J' hend update tmp set name=@root+@name where id=1
select name from tmp where id=1