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

admin

查找文件的语句
8 h7 c  {$ U5 ]# ]
CODE:

drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
! d# Y- w( J2 o! R6 g" ]5 p[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
' j; l8 J/ K8 ^8 |4 E  e4 O);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
% L* x% C1 |/ ?4 f* }5 qinsert into tmp exec master..xp_dirtree @root,0,1--
+ X  P$ G9 g5 Q* ]set @id=(select top 1 id from tmp where isfile=1 and name=@name)
" R; A2 @* _/ t/ W! M. Zset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
% \- V3 _0 p( J% R' _: |while @depth<>1
begin
! |% i8 H8 b( K7 \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)
' I+ r7 b# ~) j9 ]set @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
% _* m% i7 J; {! F- lselect name from tmp where id=1
8 G' ?- y& d- j3 q! p0 T  G2 r
查找目录的语句
! e& J* U1 ~$ Y& H" V
: {) H( e& {8 X1 t- T. M) r# ~
8 ^4 U+ A; O* p( r( dCODE:
# o, f5 [  _9 b( |8 p; g6 f2 R( l  J

drop table tmp;
/ Q  u5 y9 U; t+ F9 \# Fcreate table tmp
(
. P8 ~! G7 t: ?! u[id] [int] IDENTITY (1,1) NOT NULL,
8 j% J! D$ Y' N5 @$ x; ~4 D! W+ I3 x[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
- p; r3 `% N; X9 g+ T);

0 o+ [$ ?" @  `1 Mdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
6 P5 j5 Q9 R5 y7 f" nset @name='donggeer' -- directory to find
$ Z) v1 I2 L6 A0 Zinsert into tmp exec master..xp_dirtree @root,0,0
7 E% v3 {6 o5 H) B, C, Bset @id=(select top 1 id from tmp where name=@name)
2 Y+ `3 f) J& w. Cset @depth=(select top 1 depth from tmp where name=@name)
' E. L9 A% L( c( Z+ jwhile @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)
; P- Z2 q  j3 z$ @5 G; |set @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
- Y, {( [+ i0 I  v, v1 ]! eselect name from tmp where id=1