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

admin

查找文件的语句

CODE:

+ |7 X( n% a* @6 [drop table tmp;
create table tmp
" ~8 }# G  f. O/ y, _(
[id] [int] IDENTITY (1,1) NOT NULL,
, e/ f8 H; @9 f/ `. N2 F[name] [nvarchar] (300) NOT NULL,
! ~6 p+ A. u6 C3 D" p( M[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
2 q' J, }; g  o3 I; Q" V% y( \);
( D% `+ j( M; ~* E: ?2 Z3 K. Z
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
3 v* \, a2 a1 Z2 A' N. R! \/ Iset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
& I1 R& F  f7 o/ mset @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)
+ z& p  {% x3 H/ g3 x( `while @depth<>1
* j4 ~9 C! N  K# fbegin
set @id=(select top 1 id from tmp where isfile=0 and 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
" |0 b% l+ h& }/ aend
update tmp set name=@root+@name where id=1
6 }  Q; S& J1 h+ Oselect name from tmp where id=1
6 g2 ?* `- Z; b) q/ \
% C; h, H# P0 R5 M查找目录的语句
* r, {/ s1 |& m. y6 J4 a2 y+ G1 ^$ p
9 G$ i3 ?" G+ s" |  F
- I/ g. f/ I! \2 BCODE:
2 S5 f2 v' L' b
+ ~  Y# Y; H! z+ o- H
. |* s- U% n/ k1 }& s+ T3 I9 ]drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
/ c5 }9 I  m2 y9 \[depth] [int] NOT NULL
6 f# X, b# g  ]/ I. B6 v* N);

; B3 c  l9 K% Wdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
, A" v% A6 u+ `9 U5 d3 B" X" kset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
6 ^! n' F, U, w8 nset @depth=(select depth from tmp where id=@id)
$ m6 s4 ?5 t6 v! ~( W0 ^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