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

admin

查找文件的语句
: ]7 v" ?" \' d
$ |# {1 q: h! r7 _CODE:
0 N8 \% P: Z2 F4 ^+ c
% Q& i* J4 Y  w/ E# Jdrop table tmp;
# ^" f/ s/ Y" `% z' C$ h  ~1 ?create table tmp
7 Q& [5 i+ F* [% H(
[id] [int] IDENTITY (1,1) NOT NULL,
0 P5 j- {/ \" ~' }' A[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
' [9 Z: y$ G: s+ V( ^+ jset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
6 X9 T2 ^2 H( \9 X1 y* }insert into tmp exec master..xp_dirtree @root,0,1--
* `6 `. ~6 e0 v; Eset @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
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)
9 s* k8 q5 v3 gset @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

查找目录的语句


( a  ]0 Z3 m8 w* Y* d) GCODE:

, e3 ?0 W3 ^3 k- l) e7 Q
7 f8 j3 V+ A$ {, ]9 Ddrop table tmp;
create table tmp
(
1 R( K( k8 {3 m* q7 `' }9 w3 v[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
8 X, |. G0 r% L* r);
: n6 o1 L. D$ E+ N$ N* l$ H
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
0 }) ^6 i1 s- j; n2 v3 P! Zset @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
3 h. g3 E7 P. g! V8 Bset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
  B9 q% f  ]. v6 S2 p) X  `while @depth<>1
begin
/ H+ Z" F( R/ \- H: S9 D& F4 mset @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
9 y! r, P7 _- ^select name from tmp where id=1