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

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

[复制链接]
跳转到指定楼层
楼主
发表于 2012-9-15 14:42:56 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
查找文件的语句
1 o3 O. z: I; N, n: Z$ Z) }6 _1 k# v$ d0 q
CODE:9 |1 G/ r, A1 Z$ l# a

' ~( o3 n4 a& m2 @: mdrop table tmp;1 K* i7 L4 _# D1 d
create table tmp' F% a7 P* g. ~. C4 P5 T+ s+ H
(0 h( L( V, ?0 s8 l$ d, E
[id] [int] IDENTITY (1,1) NOT NULL,
3 |* w) O# z& x/ w[name] [nvarchar] (300) NOT NULL,8 ?& _/ _# C3 z9 T8 W
[depth] [int] NOT NULL,* S  I3 w8 }) k! D3 d3 c
[isfile] [nvarchar] (50) NULL
- i. W% S6 E' i);! S" n" R9 N. X

1 K# Z/ S5 N9 K( e( w: B% wdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)/ A% B% i4 ?1 S9 i# a7 f% o/ {
set @root='f:\usr\' -- Start root, G0 b& b% U9 n1 D  K  `" e& @
set @name='cmd.exe'   -- Find file  O' a7 n; h% H% Y2 g  t- D8 U$ M
insert into tmp exec master..xp_dirtree @root,0,1--6 Z6 t, N  [8 z, U7 G4 ~  F
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
* x+ w2 f' C, `$ U  A- Lset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)" z8 J4 E1 U7 K- L% D( Y, `- A1 D
while @depth<>1 1 U$ K4 c5 Z4 \
begin - ?. g2 g4 S0 U# j5 v& M
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) 3 w0 k1 i, Z& [( G* B$ H" `
set @depth=(select depth from tmp where id=@id) 1 t# ]3 j* Z/ p8 i* R8 }
set @name=(select name from tmp where id=@id)+'\'+@name
9 w0 U1 R+ u! ^* k3 s4 G7 ?9 Cend
# Z" [4 z% {5 i- u- ]4 o2 Y1 @5 hupdate tmp set name=@root+@name where id=1
" d! S/ Y3 Q. N" M3 p/ Kselect name from tmp where id=16 {" q) H3 }' H. G- u# _2 X
" }0 _( L( T1 ~4 z* {/ S
查找目录的语句( D) C6 ^0 N# f7 v( I

) q; P; H1 B& T6 g/ Z: }" S$ k/ [1 Y  X/ G+ V) m* s
CODE:3 q+ N( z# f* j) f5 e: ~
0 {: Z7 ?. ]+ ]: n5 \; E6 q
# i5 O6 |; }+ O: ^. m/ T) }
drop table tmp;: h2 T3 Y% j. m" N# ]! w: z
create table tmp% Y& Q! P5 b6 v6 t+ T7 B
(8 b) y5 V1 p3 n3 O' N% k5 r
[id] [int] IDENTITY (1,1) NOT NULL,
$ ?, S& Q8 F1 R( k8 d& }[name] [nvarchar] (300) NOT NULL,: q& w% z+ j9 V  ~  E0 d; L
[depth] [int] NOT NULL
; N9 Y* x: Q7 b8 w);& w6 M8 D+ p7 b2 i2 y0 p

5 J$ Y& x5 }- t& S! p8 Rdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)& D; V. ^* W" H* Q4 r6 u, e
set @root='f:\usr\' -- Start root- c2 I/ `+ o. A1 R$ B
set @name='donggeer' -- directory to find
* [3 v* J$ K* u0 binsert into tmp exec master..xp_dirtree @root,0,0* _* j% p2 U5 N9 @3 s
set @id=(select top 1 id from tmp where name=@name)
. s+ B  k8 C- i9 S0 aset @depth=(select top 1 depth from tmp where name=@name) 8 ~6 Y  o! A5 B5 R" o" R! G
while @depth<>1
3 t! K- z' A' D; a( O$ bbegin 3 u1 I- C; P! {1 y
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)- v8 W0 C  D: A- y
set @depth=(select depth from tmp where id=@id)
9 i3 B0 `( g( h* v0 x0 T! Iset @name=(select name from tmp where id=@id)+'\'+@name
6 v$ D' _& g) b: Y8 Q- pend update tmp set name=@root+@name where id=1
7 b5 Q# |* d0 F( Lselect name from tmp where id=1
回复

使用道具 举报

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

本版积分规则

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