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

admin

查找文件的语句
% f+ d; a5 s4 v8 `5 a  V
4 J: F  e6 {+ X; W& BCODE:

, E0 m& v( n& udrop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
) w, I  A- Y$ t! J2 C- \[name] [nvarchar] (300) NOT NULL,
! O9 Z  W' N/ K7 i, y1 p[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
/ w3 i5 G: K  Z; Uset @root='f:\usr\' -- Start root
6 Q/ D0 m, n9 m' j* W& a% C8 s& k" rset @name='cmd.exe'   -- Find file
- N4 }' [. B# l0 tinsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
# Y& J. {1 k" I" \set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
; w7 ?, B3 N9 ^& f4 c4 c0 l. Ewhile @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
3 i7 e9 I: V4 u/ i! q* j, `end
* @6 R9 ^) y) G/ S$ ^: Wupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

1 ~2 b9 m: h' D' B0 p) r- q+ \% c8 n查找目录的语句

4 c; R( g7 j+ Y. f( ^/ P
% I7 K! |! a1 v3 a, hCODE:

2 l4 y+ I# A. C' `$ Q
) p% _* c$ s7 q: D+ ddrop table tmp;
create table tmp
(
$ \/ s* L% u! a7 X7 h[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
4 _% e- m% J7 E5 c);
9 [( N; H: {* @
6 Q" R# F0 |9 [" b/ v/ y9 l8 udeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
; q3 p% V+ I+ I  ]3 s7 l; w2 gset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
3 U  e- N9 i: O; Jinsert 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)
) v6 |& e$ [: g9 hwhile @depth<>1
begin
9 J, g2 ]  K7 [6 I% Lset @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
! I2 n+ L. @9 j: H8 aend update tmp set name=@root+@name where id=1
select name from tmp where id=1