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

admin

查找文件的语句

$ K% P: ?4 D( q6 U: q* E! l2 [CODE:

drop table tmp;
create table tmp
$ w) X4 h$ A7 R% s8 j: o(
4 b& ~) p" o/ K' J- z, S6 c6 r[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
  B1 p8 T+ A4 L) m& \2 r[isfile] [nvarchar] (50) NULL
) {* ~/ w2 I& ?, `# W- w);

; [5 |, R$ z, d. Sdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
. ]" c% e/ I, d% p( B. q2 Iset @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
begin
# L7 J5 o0 f. Y7 @; k& Iset @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)
4 H5 Q8 S0 A* m7 y$ A( F# r! `; Yset @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
2 d  _* u: n6 }/ [" [% p4 `! v3 \select name from tmp where id=1

3 p7 P9 P" X# M  S, n0 R4 d查找目录的语句

& e4 t$ j: X2 W! u
CODE:
/ e  m- A% l6 V( Z# [+ }1 w' Y- c
' \% A' Y  F/ s( v- y
drop table tmp;
* b' J( W+ E9 w" w* p7 _create table tmp
3 T  S: p2 X0 c" \% y/ d2 `(
/ s) |+ H7 `2 B' l[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
: c9 N) X6 @: B( B/ \7 G# K4 e);
/ j0 I; W( \' T: l9 @
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
) o* }- o6 |" r- E: ]set @name='donggeer' -- directory to find
; E8 I9 R( V; h4 F; Pinsert 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)
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
select name from tmp where id=1