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

admin

查找文件的语句
4 b  G; j$ c3 }$ l9 l
CODE:

6 O* s4 j% F% O+ T% jdrop table tmp;
create table tmp
) i" {8 K9 R3 D8 d/ l/ A0 y5 o(
[id] [int] IDENTITY (1,1) NOT NULL,
4 v" h" w* P, O, Q* H' P% B[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
8 J7 N4 U" C! E5 t& g[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
; d. y2 |: d( S9 V4 x5 r1 Gset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
set @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)
9 d2 H9 G! d9 d0 }while @depth<>1
' _" R* ]/ ?$ l/ u' U+ ybegin
1 d. D3 W. T8 M  v2 b( V- sset @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)
8 _$ a4 F" Q4 v1 K0 m8 o: r4 `set @name=(select name from tmp where id=@id)+'\'+@name
- {- Z) \. h. ]: Nend
update tmp set name=@root+@name where id=1
. e& j( }, f2 x) x% y$ E6 M  }select name from tmp where id=1

, k0 {/ X6 ^  q0 q) u+ j# T查找目录的语句

# `) Y$ D! {3 X6 X$ B/ W, f
CODE:


" U5 ?, p8 v: Vdrop table tmp;
4 h4 {" y" n* m& k4 Wcreate table tmp
(
; O$ k! j2 z" L& O/ W! J[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
3 b- f( W7 [) Q6 Z4 Y# {/ D# U[depth] [int] NOT NULL
9 j5 f3 |) G7 a);

declare @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
4 Y. j# J5 |! P" pset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
9 i3 H, A3 L5 R. H/ `7 J& P& iwhile @depth<>1
begin
9 {! k# B) R; `) }. aset @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
4 m5 w# N5 U1 h  x: Vend update tmp set name=@root+@name where id=1
" T3 \( j! }8 \. i; g, w: ?select name from tmp where id=1