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

admin

查找文件的语句

CODE:
% u8 W+ u& V5 x# e. X& O9 V+ s
drop table tmp;
( |% N! n$ S, tcreate table tmp
(
8 N+ f( R) P: P2 Y0 T[id] [int] IDENTITY (1,1) NOT NULL,
8 Q9 U4 s; {3 o# n5 b4 A9 K+ S[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
3 P4 m! e( V% V6 n8 h[isfile] [nvarchar] (50) NULL
7 q1 w, J4 e8 j. C" T: V0 P);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
7 ]- f1 `; n5 e3 d/ Z% n+ Y8 M9 Sset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
# e- d2 t1 e6 V) _" f( o8 R9 Rset @id=(select top 1 id from tmp where isfile=1 and name=@name)
; M+ {$ e% L$ f! K$ |set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
- ~2 V( b0 H" I+ D7 u" A) i4 jwhile @depth<>1
, T4 L& u: M6 O( t/ ubegin
& e8 T- H5 ~2 J; w5 Uset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
, K( f. h9 _5 Jset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
$ m$ N& V/ U0 u$ H& D( J2 D6 X5 Jend
update tmp set name=@root+@name where id=1
select name from tmp where id=1
( B1 H. t$ z3 B' o
2 U/ Y2 U* q# K8 s查找目录的语句

' d& Y8 j* x8 [! c
, Q: f  |4 h) s; B  K" i% ?# a/ }CODE:
1 |5 i1 R5 l! S& N, g' n) [) P) z

# w3 L+ S5 Q4 j# ]6 D+ vdrop table tmp;
create table tmp
6 x. \: P3 {4 k(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
! f9 |! Q& l5 h, T[depth] [int] NOT NULL
+ f4 F6 _6 u2 g# A" O);

6 w7 J/ I" p/ A. M7 Ydeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
$ L# d% G0 y- V' _2 H1 [7 u# Uset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
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)
( ?. g1 i6 T1 y% q0 [while @depth<>1
begin
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
end update tmp set name=@root+@name where id=1
7 \4 d9 M5 }9 r% w8 Jselect name from tmp where id=1