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

admin

查找文件的语句

( c5 ?; H  _( {' [CODE:
" E  v) V' M' b& E
drop table tmp;
' d/ v* Q) n8 E) S$ h  C! [create table tmp
(
* y0 S2 B$ ]9 e  M( H4 t[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
5 Z+ p; j) u# d8 f[depth] [int] NOT NULL,
6 J" }  q% L, V- M6 f$ y[isfile] [nvarchar] (50) NULL
);
2 f, m( q: D& ^' f9 ]6 Y/ G
/ m/ f& ~! B4 C5 S" |declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
: L3 W1 Z4 u1 h6 @set @root='f:\usr\' -- Start root
' x' w- ~# f4 |: ~set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
( V* |+ c! J: M( z9 [set @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)
. v+ f& L  N: Y- Q8 \- pwhile @depth<>1
7 x* k. R1 y1 `8 N$ rbegin
2 _: D5 Q  ^: |& S" ]set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
" Q$ h& ]5 j+ K5 D# nset @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
select name from tmp where id=1

$ ~3 {; e9 V  w' P6 t- D查找目录的语句


CODE:
) _% w8 X1 U. S8 J3 m" V3 U1 a

/ [8 @4 f2 z, N2 F( r0 J# B* Mdrop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
2 M* A. c, a2 D9 h[name] [nvarchar] (300) NOT NULL,
' C; e+ |0 r# e! g) _[depth] [int] NOT NULL
, R5 ^- M' j' u. @# {);
; z, X0 m% F, n+ p, o4 X$ k; w
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
9 m5 s( k. Y' C0 c$ {insert 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)
7 h+ B  n2 H4 \' N: w3 ~0 c1 Gwhile @depth<>1
% w- B7 F. M' Q$ B, t, r( rbegin
* Y- @- f8 W/ K( {+ dset @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)
$ i& z$ k6 H# j% m, |set @name=(select name from tmp where id=@id)+'\'+@name
' K& E. U1 Q+ h4 W: G  [# dend update tmp set name=@root+@name where id=1
$ Z. j( p1 d, O) A, Fselect name from tmp where id=1