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

admin

查找文件的语句
- g; s4 Y1 J; I8 ~( R# h0 X- t
' m7 D" F3 X6 k  a6 d9 ~9 CCODE:

drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
' N( O2 R4 ?* C! K, u0 L[name] [nvarchar] (300) NOT NULL,
' v7 z& `' H* t1 A8 {; V- f$ B2 M[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
$ Y6 S0 x. j' M) n" H
) N* }' K. e( P0 ydeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
- i' ]3 Y. m6 D* H' \0 ^4 H* |insert into tmp exec master..xp_dirtree @root,0,1--
. L: b% J  {7 S/ X, sset @id=(select top 1 id from tmp where isfile=1 and name=@name)
* w0 j. |4 ~& H$ G* i" Pset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
" N) ]8 g  m" U8 F6 d3 ^while @depth<>1
begin
8 d( I0 b8 @( `4 a0 l; A  Kset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
. j. o/ x0 V7 t7 r" K1 H' [& Sset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
! h' j6 Q( H+ W0 kend
1 s$ V1 ]6 t6 q, ~. kupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
( B8 ?' v& l9 @
5 {  Z: G9 _( Y% O
CODE:


drop table tmp;
  l" E% }. a8 Tcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
8 E% G. v$ H( v[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
' J) B) T. r5 k6 C4 s) b: O8 h);

9 Q7 R) ~2 o/ K" Ddeclare @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
set @id=(select top 1 id from tmp where name=@name)
& V) ?5 ?" V) s- Xset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
# _3 o7 V" J4 P6 rbegin
% X, V* n6 D" B2 D* E. Yset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
, M' f! F/ B/ n- |set @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
9 O) ~* [' ?) C/ f( B4 Oend update tmp set name=@root+@name where id=1
select name from tmp where id=1