查找文件的语句
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 |