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

admin

查找文件的语句

CODE:
8 t% O! b) x2 v5 ]* L, c7 k
- j+ S( x$ g6 Ndrop table tmp;
  \: }' G$ h0 B6 _# O5 V9 xcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
. y) g* a) b8 A( v% v3 N- E+ i6 a[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
# r7 O/ P" Y: Z' R( N1 ^' Gset @root='f:\usr\' -- Start root
1 Z. Q- t9 s( h, e! Wset @name='cmd.exe'   -- Find file
8 H' ^0 S1 Z) R8 T' f4 dinsert 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)
& w& z( T! T1 n" w7 Awhile @depth<>1
# ~' @3 ?8 |& C$ d- G6 J9 V& w4 Rbegin
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)
+ u: ?4 }2 s$ v* }$ Zset @name=(select name from tmp where id=@id)+'\'+@name
7 D! @5 ^; j( m  L. \8 E6 z- z, A! K( wend
update tmp set name=@root+@name where id=1
& g  M( a$ L# |! L# a3 T. d  ~select name from tmp where id=1

3 x) d8 J, Q! [  u7 d, M4 _, T! ^+ C查找目录的语句
# W6 c' c; V; x" r# W/ @
+ O; l# l2 l. x' q6 h0 C& s, v2 T
CODE:

' l  c0 q8 U: |# C1 J7 B
drop table tmp;
6 u- A/ P2 |4 H; Kcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
- U0 [" M: }, Z7 V);

/ f4 e: p9 P+ ~( cdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
6 R* \0 x! y# finsert into tmp exec master..xp_dirtree @root,0,0
, k3 L9 D, |6 wset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
/ `) U( R" ], u# I+ p" t1 U1 pwhile @depth<>1
5 Y2 k/ v5 I  L+ u/ dbegin
$ L; G( e3 l5 }0 o9 Hset @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
+ |+ W% K  \$ L: S! P: H( ~9 bend update tmp set name=@root+@name where id=1
7 f4 g2 p1 o3 y2 Y. g& i/ `select name from tmp where id=1