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

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

[复制链接]
跳转到指定楼层
楼主
发表于 2012-9-15 14:42:56 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
查找文件的语句
3 E; X/ Y6 L% t- i$ t# O; I: |2 _% x8 S7 [: e1 z
CODE:! w. c9 w! l0 z
9 f4 E' X3 I0 G$ E4 k7 E
drop table tmp;( f7 G( f$ u  a5 |5 C
create table tmp
# m; o6 E! y6 h8 ~2 j* |& o5 i(# L4 j8 F) v3 |9 q3 [
[id] [int] IDENTITY (1,1) NOT NULL,  S. [/ H: Q8 U# ]( z0 f' \4 \
[name] [nvarchar] (300) NOT NULL,5 M. X, {7 H9 v
[depth] [int] NOT NULL,
4 L/ Z' k! o& I# Q5 ~- [1 ?[isfile] [nvarchar] (50) NULL
7 }$ g- I% g) L  I' _- g, m);$ ]( b* A: s+ d

" Z* K, g0 d0 h7 ?) ]; ]; ?declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
$ {3 W' {& I% V+ ]( R) Wset @root='f:\usr\' -- Start root  W& F. W" ]) ]0 _; n
set @name='cmd.exe'   -- Find file
. u% K0 ~* z8 {2 yinsert into tmp exec master..xp_dirtree @root,0,1--
6 n& ^/ u, G+ ^+ f& mset @id=(select top 1 id from tmp where isfile=1 and name=@name)
& p3 c7 D: J2 ?0 qset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)+ }, u% S6 {# D$ H- F
while @depth<>1
' e$ ^2 Z7 G% S4 n  j% mbegin 8 M6 c% q  W) c) B# }7 h, a
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) $ ]" B6 n' ]) D3 h8 s$ u+ z
set @depth=(select depth from tmp where id=@id)
' @( {) y3 y: G4 oset @name=(select name from tmp where id=@id)+'\'+@name
- q! N9 K3 o9 [: x9 c. ?end
5 ?( a2 ^* b6 hupdate tmp set name=@root+@name where id=1
  n6 U0 U& u# Z* ~: A- _select name from tmp where id=1% |+ `5 H1 y$ S; h
: d8 c% p0 _. ~3 ^: f1 i
查找目录的语句+ X) j- ~+ n0 z" t6 ~. |

2 a5 Y' Y; S$ Y6 }3 t* f3 {) `0 b
CODE:
$ ]0 n5 w5 q/ i! l& a
+ u* L; A6 F% X& f+ P$ ?9 T0 L" ?  h+ I7 v
drop table tmp;4 a: G2 V! X! `/ \
create table tmp
6 k" `' C" o. R% v8 Y3 \(3 t4 G' q  k' v' _0 b0 b! y) [
[id] [int] IDENTITY (1,1) NOT NULL,
, Q+ S& q" I  g0 [: b1 C  X8 O[name] [nvarchar] (300) NOT NULL,
6 a3 s7 J" G7 W5 I4 R. v: [[depth] [int] NOT NULL
0 K; R2 t$ W% {# @# m8 O3 e7 Y);
% w: p& X; f  b6 N4 k1 V5 ^4 \+ [' t( O& S/ `
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)' {4 Z2 m, b/ ]
set @root='f:\usr\' -- Start root
& C& P3 O4 D6 b! A5 Dset @name='donggeer' -- directory to find( `7 w- a: |; U% s# [9 x
insert into tmp exec master..xp_dirtree @root,0,0
8 l' }9 z! O: b! u( V( xset @id=(select top 1 id from tmp where name=@name)
/ l, Y) k! X% P# b! v& tset @depth=(select top 1 depth from tmp where name=@name)
$ V( u; _7 Y: t6 X, ^% [while @depth<>1 " i" g8 u# a/ @9 }0 v
begin
6 N, C) ^1 e/ |2 N. v% e' nset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
0 W6 n  t: f5 h: }( x' G- |) aset @depth=(select depth from tmp where id=@id)
% R( n( v' v) u9 q8 z$ eset @name=(select name from tmp where id=@id)+'\'+@name ) b, Y: k; x! N& I3 o
end update tmp set name=@root+@name where id=1
0 J' m, S: a8 _6 Q6 h! }( Cselect name from tmp where id=1
回复

使用道具 举报

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

本版积分规则

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