找回密码
 立即注册
查看: 2900|回复: 0
打印 上一主题 下一主题

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

[复制链接]
跳转到指定楼层
楼主
发表于 2012-9-15 14:42:56 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
查找文件的语句
% l1 S) K: a) M) I, S$ ?; z' W) R' c1 |- Y
CODE:
3 k1 S  M! ]6 B$ I
* G2 Z9 I4 w# R2 @: W- Bdrop table tmp;/ v2 H; I/ [( w% [" e
create table tmp
! s7 w! w! n# X& \) g% u* C& H% s(
* C. h" U! y$ X; d. y& F, p1 e$ O& }) s[id] [int] IDENTITY (1,1) NOT NULL,+ _  {+ K4 J, k. Q- N
[name] [nvarchar] (300) NOT NULL,
2 c& I/ g6 Q9 Q: D[depth] [int] NOT NULL,
* e0 w$ _1 Y/ M4 x$ M[isfile] [nvarchar] (50) NULL- y* u% y, }. r9 ^7 o
);
( s9 k# ~  t2 Z6 w# \1 }! b: ]& c$ d: V) t8 `8 E# L) n
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300), t* d6 Y7 D4 j. f% O) u9 L
set @root='f:\usr\' -- Start root
9 h6 e2 c3 b- U! ]$ A+ [6 Pset @name='cmd.exe'   -- Find file5 L2 Y% T4 s" H) ]5 |
insert into tmp exec master..xp_dirtree @root,0,1--6 F  y" @$ [2 p- S9 B7 ]- W' r/ K
set @id=(select top 1 id from tmp where isfile=1 and name=@name) ' J' `- Y& K4 ^3 ^$ [8 a
set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)' _+ f0 ~: w/ X: W9 A# h( K( l
while @depth<>1 * s( ~# w' R9 v
begin
5 Y& @5 `1 A+ n) l7 C3 p- p& sset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc) 2 y. J' c1 s2 W4 ]" e
set @depth=(select depth from tmp where id=@id)
! c( l$ W9 \" N2 z4 i. s/ Lset @name=(select name from tmp where id=@id)+'\'+@name
* f# s- F8 g" B/ @end; Y$ ?$ o+ H2 }4 a8 ?" E% V. O
update tmp set name=@root+@name where id=1
# Q% n& x9 D# M& E' [8 ~& {2 C! |select name from tmp where id=1
! K  d# t- o* w3 z( j# H% W( E% [8 K# K. Y$ ~
查找目录的语句
4 X6 w- B& s7 H, E: h' U5 G+ B5 l' o$ B6 Q- m2 M. z
. B5 V7 n+ ?4 [6 r
CODE:. N7 U9 l. G5 ?# _0 c, H
; q2 e* C4 [/ @- ~( F: W

, H  O# Q  ?; j! O$ gdrop table tmp;( v0 t/ l6 m& R
create table tmp
$ ~0 m% T. g/ s0 L0 _3 y' y(
2 F: M0 _" o3 J; \[id] [int] IDENTITY (1,1) NOT NULL,0 |% x0 }  I" o8 @3 |$ M
[name] [nvarchar] (300) NOT NULL,. c/ P# J# q9 {' X0 H/ B( h
[depth] [int] NOT NULL
8 E* `5 k) N: d/ `, X1 g; S);
, y& p- i; }; K9 `  a  y. Z% E: y& e0 _- U, I2 l/ q/ y+ J
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
+ J7 X- @5 l& h# W% H! t! n: J; ?set @root='f:\usr\' -- Start root
$ P$ m; W& i8 Fset @name='donggeer' -- directory to find
, C6 a0 |7 q, m: K6 _insert into tmp exec master..xp_dirtree @root,0,0
' q, ?; _+ y. O% S& ]+ I' Q/ Q0 zset @id=(select top 1 id from tmp where name=@name)
: f/ i0 A/ ~' ?* Mset @depth=(select top 1 depth from tmp where name=@name)
, T; S5 E5 A/ qwhile @depth<>1
) b& X' o$ G+ {; Z9 nbegin * H$ I, E- a3 y" j
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)8 e) q) l: f4 D0 t
set @depth=(select depth from tmp where id=@id)
" X" z+ \4 ?8 R8 B& ~, s/ T5 Rset @name=(select name from tmp where id=@id)+'\'+@name 8 d8 e. V7 g& d( q0 o8 `* A
end update tmp set name=@root+@name where id=1
+ V1 Z! s& F" X& ]& l$ `select name from tmp where id=1
回复

使用道具 举报

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

本版积分规则

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