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

admin

查找文件的语句

CODE:
' j0 a2 D& c9 X" z+ W3 d
drop table tmp;
create table tmp
(
1 Z% q: x) c) H/ _/ m; f% w) {[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
  ?$ q) N) a5 r: l: t( e. ?9 G[isfile] [nvarchar] (50) NULL
- _) n& i5 s! S; L);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
/ h$ w" ]- o3 T' \1 e. m9 c" I* gset @name='cmd.exe'   -- Find file
( q3 [& g; N/ J- D4 hinsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
3 Y$ m9 `) u5 Q& l- {& ]  s: Qset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
begin
7 ^3 G5 x3 E/ B% l9 T& ?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)
0 |& L6 d2 C0 M6 c/ U. ^set @name=(select name from tmp where id=@id)+'\'+@name
/ A: ^1 P( Q$ B/ i: ?end
update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
+ M1 J# E3 L& H' J, F
. M6 o7 X# a  w9 H
CODE:


drop table tmp;
5 ^/ I* c, c. s" Z5 Kcreate table tmp
% v1 f' Y) |# x+ s0 |2 E(
$ p1 K1 X2 S% z: J$ o[id] [int] IDENTITY (1,1) NOT NULL,
! z& Q, Y/ ^1 I[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
7 r+ P  Y$ [$ d5 O) d5 u0 d9 Jinsert into tmp exec master..xp_dirtree @root,0,0
) X. W8 n+ h" z# c; N' q9 U+ Jset @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
' K1 L& z/ b* K  L. @select name from tmp where id=1