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

admin

查找文件的语句

* C' b% ]3 Y' j% Q! G) qCODE:

' M4 P6 [* ?3 ]$ _" o: a. zdrop table tmp;
+ f3 {! s  g( _8 w( b2 Q( vcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
: |  E: h$ O0 w1 c  i0 g- b[depth] [int] NOT NULL,
2 Y' k+ g+ I, w- B$ r% O. s[isfile] [nvarchar] (50) NULL
7 p, D6 P8 V; E! |);
9 t0 I2 [7 N" [* h% c3 ?
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
# |* F6 J3 B# b  s5 j3 c2 x7 Ginsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
" m0 j0 C( B) ?" l" nset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
begin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
. d* J: e6 r0 V# Qset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
& R. y; F% _1 a) Bend
update tmp set name=@root+@name where id=1
: y6 L+ ^. P2 p6 F0 o/ ]select name from tmp where id=1
" `1 q" F6 e+ W5 |* n% V/ M* r$ j# ]
查找目录的语句
( }! ?2 M- W' ]" ]0 K& s7 q: u. w

CODE:


drop table tmp;
; ]6 f7 ^6 Q7 C1 g, z1 M2 V  R+ V- o* Vcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
& K; t( k; l0 Y& H) [; s[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
5 \2 J6 V! M! Z6 k);
0 u* _" z+ g1 `, `) [( e
4 E, Z% |( o7 K# Y: d  b$ qdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
7 w, L$ Z6 B0 f# e# ?set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
6 P+ n* }1 |; Ninsert into tmp exec master..xp_dirtree @root,0,0
: R% x& G3 b# N# ]( z- eset @id=(select top 1 id from tmp where name=@name)
; Y, v  D+ |+ ~' eset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
2 q7 V$ a/ m$ r% Wbegin
7 B" @  |. [% t8 Mset @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)
8 S& n( C+ p' B3 F9 J4 Y2 Hset @name=(select name from tmp where id=@id)+'\'+@name
* F0 ]: L' Q: t* `# v8 W7 Qend update tmp set name=@root+@name where id=1
6 x; M, A, g1 X/ V1 `/ Aselect name from tmp where id=1