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

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

[复制链接]
跳转到指定楼层
楼主
发表于 2012-9-15 14:42:56 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
查找文件的语句
, R9 U* C2 }7 T1 B, V7 V1 l" n7 ?
/ ^  Y# R8 l+ z% oCODE:
4 T  e6 u+ t" z$ y7 `! _1 `2 l0 w; k! }4 E9 n
drop table tmp;5 l& {3 `: c9 b% J$ {
create table tmp6 g; J+ M/ }! I2 c( j# L$ z4 ?
(
1 d* T4 L) G2 a[id] [int] IDENTITY (1,1) NOT NULL,: I& F8 s+ q- N% O8 U" }3 X2 {
[name] [nvarchar] (300) NOT NULL,
  @" v- I' l$ P8 A, b3 S[depth] [int] NOT NULL,
- F6 I& f( O# \[isfile] [nvarchar] (50) NULL; q( ]; _) ~+ i. I1 Y
);
- c1 Z2 _( O8 C" O' v1 [/ O- p! C$ \  \4 F! z# v: n& e, J
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)+ X4 T3 d) U% z9 R* h
set @root='f:\usr\' -- Start root
+ K8 \- ?/ @7 r3 T& _set @name='cmd.exe'   -- Find file
2 Z! O0 l7 u& N9 ~insert into tmp exec master..xp_dirtree @root,0,1--
0 x/ k3 E+ v& U, {8 u/ Eset @id=(select top 1 id from tmp where isfile=1 and name=@name) : h& w; y6 M7 v/ Y8 K9 _
set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
9 B# e" A) J  ]while @depth<>1 8 x& I0 D5 s- T& ]- _" f, g
begin
8 D" Y& `3 e$ s; S$ _2 Hset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
0 N' p5 K0 U. h6 L% E' P: }set @depth=(select depth from tmp where id=@id) 4 |% Y" I8 ~& A# b
set @name=(select name from tmp where id=@id)+'\'+@name
! r* ^# q4 v# b' Q" b" U9 }end" \% J8 d0 k! X* \( P& M% v1 k8 \
update tmp set name=@root+@name where id=16 D# H( Q# Q* u
select name from tmp where id=1
9 Z& x" [3 o& p& d: p# y
6 d9 ]; B+ Y( I8 M& r& F$ A- s! l8 P; I查找目录的语句) Y7 k$ Q; j+ i. K* ^
! S' c  n! g. h$ L0 \8 q
7 j; A9 f  L0 \; c7 {) B( f( J5 n
CODE:3 ]3 U8 D7 H7 n
$ S& J) ~8 k, p9 m% D# `

5 \6 E2 t4 a- e( Vdrop table tmp;- Q8 e, w$ R! H2 o
create table tmp+ p. G2 Q2 l6 B
(
( L/ m" p6 `' w1 }' z[id] [int] IDENTITY (1,1) NOT NULL,9 Y" b, ~" A3 W
[name] [nvarchar] (300) NOT NULL,2 [: P2 @1 \# W2 t: p$ Y
[depth] [int] NOT NULL' U" t: C1 V7 X/ R4 Q. t" X
);
  ?+ g; S: N3 p; }
6 E5 X1 _- M9 L7 N! c1 p% Edeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
5 Q4 C  E: D# g* ^+ X& _9 Zset @root='f:\usr\' -- Start root
% \) }, Z' k& {. c8 |) G7 ]9 Lset @name='donggeer' -- directory to find
" F) P5 ?5 F9 e: N" [) iinsert into tmp exec master..xp_dirtree @root,0,0
# |& {) ^! Q- b3 \: }  ^. }set @id=(select top 1 id from tmp where name=@name) # A9 k9 v, |9 j4 k% J' |" K
set @depth=(select top 1 depth from tmp where name=@name)
- l5 S) a8 n: ^9 S' r% Wwhile @depth<>1 & Z/ R/ B" r8 j" E0 n4 O1 K# M
begin * h6 [9 _  A0 W4 X; D. K3 f* b! @- S, G9 _
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)8 ]; Q2 S& ]5 p
set @depth=(select depth from tmp where id=@id) 0 k. h6 D- J2 T( o
set @name=(select name from tmp where id=@id)+'\'+@name
7 r3 ^% \2 ?( K/ M  oend update tmp set name=@root+@name where id=1
$ d' j, ~* I+ J4 h* ]) ~select name from tmp where id=1
回复

使用道具 举报

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

本版积分规则

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