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

admin

查找文件的语句
/ z+ u/ |8 x: _& P' ~/ \% T
CODE:

5 p1 r) y1 w6 T6 E! ]2 E' p' ?drop table tmp;
; E$ o/ I% O: o3 z: D* ucreate table tmp
* W  f3 z; |, f(
# R# z. G$ J. b* [- F% Q$ u' Z[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
5 Y) C, U$ r# E( F5 u; J5 N/ o[depth] [int] NOT NULL,
, Y1 L( r9 P4 Z[isfile] [nvarchar] (50) NULL
);
  H. {% u( I2 z+ e! N% u
* s% {, H6 w1 v4 |$ k$ J/ N/ Y2 Adeclare @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--
  k% C& {# J+ k3 q' s! K3 q: jset @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)
set @name=(select name from tmp where id=@id)+'\'+@name
9 Y7 }- |9 B0 n+ A9 `, k% _/ @end
update tmp set name=@root+@name where id=1
3 H! R5 X1 D# n- u) @; P& i$ W9 nselect name from tmp where id=1

查找目录的语句


0 |' j) S3 A) H9 C' cCODE:


drop table tmp;
0 o: j& ?5 |/ E5 `7 U& t4 Jcreate table tmp
2 r# S/ p/ w, Z7 z(
[id] [int] IDENTITY (1,1) NOT NULL,
5 S% l( [6 C0 T. E5 o[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
+ B, r5 ?& A* \( P1 x# wset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
: B8 ^" N( `# C, z3 p) Vinsert 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)
. {- g$ N, @$ ~0 r6 p: }9 `while @depth<>1
9 y2 V7 o$ T9 A  X  rbegin
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
" W) R/ U# @7 o# ~- U+ Send update tmp set name=@root+@name where id=1
select name from tmp where id=1