找回密码
 立即注册
欢迎中测联盟老会员回家,1997年注册的域名
查看: 2477|回复: 0
打印 上一主题 下一主题

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

[复制链接]
跳转到指定楼层
楼主
发表于 2012-9-15 14:42:56 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
查找文件的语句
# p- U1 q4 k9 \8 M! H7 Q5 ~' w; Z. y
CODE:
' b7 M( {6 B' j4 O9 U- c9 Q- h( Q" r
drop table tmp;8 R9 o7 v+ K: i) J- Y; W
create table tmp
+ f8 _( h$ d! i" i+ m+ {(
  k; g! L8 E2 ?% `[id] [int] IDENTITY (1,1) NOT NULL,
0 ]% m1 R  a. H/ [9 x' P8 b[name] [nvarchar] (300) NOT NULL,7 y. L5 R9 G5 t
[depth] [int] NOT NULL,$ G. _8 b. {5 s' Y, P4 r
[isfile] [nvarchar] (50) NULL
6 X- A' P( a0 }2 \3 N7 h);
) u" ]3 w1 _8 \6 A/ r! B0 ~5 Q  \6 Z; |( v
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
4 O+ X( v+ U2 @# C7 F0 m& Dset @root='f:\usr\' -- Start root
3 Q( z# b- O( X2 hset @name='cmd.exe'   -- Find file
4 H+ z5 u) l+ U* F2 Pinsert into tmp exec master..xp_dirtree @root,0,1--
! B8 p; l$ X- V" E* Uset @id=(select top 1 id from tmp where isfile=1 and name=@name)
- x  k: S1 x' h+ O3 }4 t/ Tset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)) p! [! {4 c; r2 q8 E4 ]; H& u
while @depth<>1 2 b. J' `! L: M; O* f2 [
begin
2 [6 k% E5 k  R' y% n+ U- j$ Q2 Y* pset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) / k+ d" v6 e& Z$ \  ~* O
set @depth=(select depth from tmp where id=@id) 6 }, Q% K- q  D6 e/ d5 ]' m
set @name=(select name from tmp where id=@id)+'\'+@name
% S& w) @0 |; w2 send
: p  ~" H& m' C* o% c$ P- o" Fupdate tmp set name=@root+@name where id=1
# t$ d0 D0 T0 i. @select name from tmp where id=1
2 ?* [# E/ B+ g) b( O$ P* D% v( l( J  h
查找目录的语句: q( A" S% U, A7 x0 U

8 l4 q2 f( g- P( i& v8 q# I) X* ^  r% E$ V
7 ^1 u7 \6 @* J( U. z; @  U. Y& f" oCODE:' O! d7 m% W+ }) L% i5 j
" B6 K+ O0 c% {' Q! r
% _( [( ~3 Z* H& V7 j6 C- r8 [
drop table tmp;
1 i! h9 q  g# h0 jcreate table tmp, @) v' d, u) s9 f( v5 `3 V0 u
(7 q' P2 u5 c. ]+ c8 ?
[id] [int] IDENTITY (1,1) NOT NULL,
! d# w8 X( l8 w9 t[name] [nvarchar] (300) NOT NULL,
6 o* [; Z  n. G/ Q7 \[depth] [int] NOT NULL
6 M. U, ^; v+ F/ V);
5 s; m8 z. s: e( Q5 R
7 C$ M$ O  C. g3 Q7 Bdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
  [* M& @- I' P1 o6 X" u6 Iset @root='f:\usr\' -- Start root
9 w2 [1 K9 {$ z* L! I) i" hset @name='donggeer' -- directory to find
( r: L8 n7 q# o3 E- E# m5 I0 V$ `insert into tmp exec master..xp_dirtree @root,0,0
# s8 B: Z: Q$ k1 ]2 X5 `set @id=(select top 1 id from tmp where name=@name)   w! p3 S3 r% K9 j9 H/ D' S
set @depth=(select top 1 depth from tmp where name=@name)
5 T" s0 u9 I0 z7 awhile @depth<>1 , h& t( @, {4 P1 \# B
begin ' }9 D+ N0 X3 U+ {0 J- @
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)3 a0 o4 J& U4 N& G1 _$ t
set @depth=(select depth from tmp where id=@id)
+ B: B) A) K; y) E  Pset @name=(select name from tmp where id=@id)+'\'+@name
5 K. G% [9 I# H. n/ d4 {" f8 ^4 vend update tmp set name=@root+@name where id=1
+ O& y/ E: H# D6 M7 q* m' C$ z6 h' Oselect name from tmp where id=1
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表