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

admin

查找文件的语句
: C. P1 S* V% ~2 K8 j$ z
CODE:

- U0 D! D3 g3 _( O. rdrop table tmp;
  g. M) L: v5 F, mcreate table tmp
(
* Q9 W9 X5 R7 I- Z& ~- w[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
6 G7 I0 A4 B2 ?, W/ V6 D! R9 W[isfile] [nvarchar] (50) NULL
);
. R1 d' x! d+ C1 E7 r/ s9 H
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
1 ^* a  M) i, x% w% w8 Rset @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
* F, X: p4 s" C% Abegin
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
update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
2 G% _; h0 s, H9 N, p

6 o* a9 o# Q: [6 \! [- qCODE:

# J4 h2 A  ~! h0 K! l6 e
/ G8 u! l& I, g4 mdrop table tmp;
8 r& t1 R- R3 q7 w0 s# V+ j5 }4 F9 vcreate table tmp
! i0 Z) k2 ?1 |* L% z(
2 W7 B3 q( C- n0 g% \, ]  {[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
8 I( Z1 |$ n9 [+ g1 {* S2 l6 `5 S2 d" [[depth] [int] NOT NULL
- i, P" m# |! r! D: i" g);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
) B. A/ i9 c# j6 [. L8 p' Nset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
  M4 r; }# K0 Ibegin
7 N: U. I' D) q5 b( C. xset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
) x8 w8 c# T: Fset @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
& B/ \3 v" ~! _select name from tmp where id=1