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

admin

查找文件的语句

CODE:
7 q3 v* k5 F2 |$ _' v8 m# d* m
% ~$ [! i/ v+ R* b: T; ldrop table tmp;
create table tmp
(
* A2 m1 a! p6 y5 l6 z, V[id] [int] IDENTITY (1,1) NOT NULL,
. X5 d2 F+ H& G1 g9 t2 m[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
; {7 p! @/ A# H);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
/ ~( l2 {. W- Gset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
+ l  [3 H/ j1 i! t6 m1 {set @id=(select top 1 id from tmp where isfile=1 and name=@name)
) P" Z" ~$ H5 F" C; Wset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
0 i" N$ C& v/ Y/ \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)
- ~/ G# u  q% @: I4 O' E4 k  b9 iset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
0 B' ]3 v, M# o1 l1 J7 T- \end
update tmp set name=@root+@name where id=1
0 L3 {5 S# E% [$ j) n( r: a# m+ q$ vselect name from tmp where id=1
& h5 F) x& y  J. W8 S9 q
查找目录的语句
+ D- B  U; C, D3 G" f' g% ^1 _

( r! e2 Q: I8 r. \% hCODE:


drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
' ]1 {5 ^% ?* p[depth] [int] NOT NULL
);
+ D5 [& \" ], U
( A1 y- i: Z% K3 D% U4 Tdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
  S* t/ [; o* k5 w1 C8 e' iset @root='f:\usr\' -- Start root
6 m: }$ X- ?6 a- W- [" n, v2 nset @name='donggeer' -- directory to find
+ u* [! q; k; K4 q) D* 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)
% S. R  i0 _% L5 P% [0 F; l9 S* nwhile @depth<>1
6 r5 x' y1 q7 Sbegin
set @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
5 w" `1 _. X, |( P3 }1 ]" I; |. mend update tmp set name=@root+@name where id=1
6 h- Z2 {/ f+ `; a* gselect name from tmp where id=1