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

admin

查找文件的语句
, S& d5 i3 W' _9 K% b% s. ?% L
3 c% S' X7 o2 l6 `$ ]CODE:
- J. D/ w5 [; F
drop table tmp;
create table tmp
/ q* j( k5 @+ A, u5 }(
[id] [int] IDENTITY (1,1) NOT NULL,
' n2 R" U! S7 W% c& T# N+ ^! o[name] [nvarchar] (300) NOT NULL,
( `2 Y: b% ?& g[depth] [int] NOT NULL,
2 ?( t; U0 m4 e7 e- \% M! G[isfile] [nvarchar] (50) NULL
/ A. t" \8 b6 u  ~& Y8 n9 x0 A);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
# M6 W. M0 ]9 `% X+ X0 y; v$ P2 Lset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
+ R( E2 n9 H4 Q  d& M/ Sset @id=(select top 1 id from tmp where isfile=1 and name=@name)
3 [: c- Y) L. G9 Q' \3 S! Mset @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)
7 Q" j! }- k4 M/ p0 Gset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
end
5 |. {; P! f! R, G% Lupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

7 k3 R8 f% r: j; B查找目录的语句


% U- f- a- d( L& a6 UCODE:
) G4 z$ W; l1 p6 b) W. T- n
0 i) ~& j8 N4 {5 J
drop table tmp;
" [2 Z, G1 w! T) ccreate table tmp
5 E: L! g% w8 g2 C) d3 n0 [) |(
7 `: n/ `: i1 y, U; I. Q" I3 x- @[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
. D* j9 k; x/ P# G; v[depth] [int] NOT NULL
);
8 c* @" V% y  Q  j7 ?+ i: o
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
0 X. a2 {8 A% \8 N" n9 ^4 Y; pinsert into tmp exec master..xp_dirtree @root,0,0
' s7 I# Q' V" \5 |6 s; c4 t3 Oset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
4 V2 f/ q# P& {' e; }" i" Fbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
' h/ ?2 J, _- uset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
0 J+ j  a" b! g6 K2 L4 T8 Vend update tmp set name=@root+@name where id=1
# f7 n. r# e: `+ }: N+ E: Uselect name from tmp where id=1