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

admin

查找文件的语句

8 S6 }% [2 e6 B, hCODE:

drop table tmp;
4 A; u, ]2 q& X* hcreate table tmp
7 o: ^$ r" w) w* Y$ a(
/ Y' S. [- G) [9 B0 o' Z, B! w[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
  E( s% ^, d1 I# s[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
9 s2 ^7 T0 \3 Z# `- j3 ~1 D/ Z+ t: Fset @root='f:\usr\' -- Start root
/ u/ X$ e3 y7 n! n; fset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
4 v5 }1 d" L' I, A  uset @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
+ S1 ?& t3 r3 K7 R# W$ fbegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
% y+ Y# r3 x% B$ F' C( e- W& Gset @depth=(select depth from tmp where id=@id)
1 q$ P! s  N# m# ?- `3 zset @name=(select name from tmp where id=@id)+'\'+@name
end
: {7 k; E" c2 Y% [8 @/ Oupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句


: v2 R* E; E# [+ ?/ @7 Q, zCODE:

* Z; M& f+ m' {! R- K2 W
drop table tmp;
/ `+ w( E& f  j3 ]6 K, Pcreate table tmp
& Q- N" R" P) z+ r1 b( T, t7 i1 y0 l* E(
; o# p: r3 b5 [) o( v9 X[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
/ v0 k) p( G& u1 y( y& h/ Zset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
, p+ Z; n' p: m( winsert into tmp exec master..xp_dirtree @root,0,0
+ ]6 {5 B1 }6 X. g, N6 eset @id=(select top 1 id from tmp where name=@name)
2 C1 k  _2 {8 b9 V+ M% y$ Aset @depth=(select top 1 depth from tmp where name=@name)
/ E9 `& O" E- E$ Rwhile @depth<>1
% i+ d) b. v; j) j' S) k7 Sbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
# Z' u8 K4 P* P! m6 c6 jset @depth=(select depth from tmp where id=@id)
6 x" W8 b1 E$ a# _set @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
- I) N# S3 K) B$ v( i9 a; Zselect name from tmp where id=1