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

admin

查找文件的语句
$ V. K5 V' `# ]4 l  _. ?* \
CODE:

4 M! [- y$ ~/ l: V" S3 Wdrop table tmp;
! I$ e" z. W! R$ J. K; [create table tmp
$ Z4 D5 F* o: |+ l+ d(
9 \6 f) h# T3 \7 l: k: k[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
% x' e7 a: T; j! T[depth] [int] NOT NULL,
# r( B2 X, K1 f, y6 z' a; _[isfile] [nvarchar] (50) NULL
- S" K( b9 \( j);

9 m3 y' A8 [3 _( o9 pdeclare @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--
* K8 s# [2 K7 P  z" k) Oset @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
  f. _8 o$ n- l3 uset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
( c0 E- C+ J/ C7 x7 @2 a( Z8 x  [set @depth=(select depth from tmp where id=@id)
0 l: I( u' r7 G! a1 ]5 b6 cset @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
! V) [. L0 i! V/ uselect name from tmp where id=1
6 K& K; q3 V& m& D8 [: m6 ^. [
/ D0 }. K2 @/ X6 d查找目录的语句


+ T  m, [' |) S) s4 b+ s& A4 ^- f6 kCODE:
" w2 [6 d9 J4 [
  Q) v$ R5 C) I/ Y. ]& D
8 p8 _$ X8 d8 T+ E- O4 _# Sdrop table tmp;
! w+ O+ K7 J: m' C: b  W0 Jcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
9 z4 S& W8 s0 l2 _
" t& w% q6 q. `" wdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
  \# X8 n# G* b5 vset @name='donggeer' -- directory to find
$ _# c3 w& K3 d) H) ]9 v' _/ tinsert 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)
. P0 V8 Y. D0 k  T2 Gwhile @depth<>1
7 a+ a8 a" p7 h7 C3 P; Ybegin
. M7 d( Y: y% D3 F; l8 H6 _/ Y) L4 }set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
# _3 j8 Y& D5 e1 vset @depth=(select depth from tmp where id=@id)
8 I$ k  D8 \% A1 Z1 l" ?: kset @name=(select name from tmp where id=@id)+'\'+@name
) ~5 n! C; b/ ]! ]end update tmp set name=@root+@name where id=1
( A* d- z) q9 @* Pselect name from tmp where id=1