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

admin

查找文件的语句
" Q7 P0 C1 n9 d5 @8 l
CODE:
) r: B$ _: f3 p/ ?
8 D6 I2 W7 s; f$ |3 adrop table tmp;
create table tmp
" M% \& @8 B$ e2 \1 B  n) K. P(
1 F! l# L- O/ A/ t0 V. m1 c" r[id] [int] IDENTITY (1,1) NOT NULL,
2 v5 h- H8 B$ X1 T2 h[name] [nvarchar] (300) NOT NULL,
4 U/ v! S/ a5 ?" ?  q[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
$ ]2 @9 Z4 C1 X' U);
0 e% C6 P$ R; {! g9 E2 U3 j
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
$ q4 N& |# v! D- _; r8 Hset @root='f:\usr\' -- Start root
1 C5 X4 M- R$ z$ ^8 |set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
+ V! t, P3 O2 c  b, d& f/ sset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
begin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
& k2 o% P  V9 R7 Uset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
end
update tmp set name=@root+@name where id=1
8 b; F  M2 o9 [select name from tmp where id=1
+ @& k& J& S5 C* q, \4 d" J: E
+ V6 r# i( ~+ S8 h2 m8 J查找目录的语句

( m2 t/ X2 r9 B( k+ _9 Y- g  p% H
CODE:
6 Z& E  n5 a$ x

7 Z7 f1 c. z) }% Z: w) a2 d  \drop table tmp;
1 t3 G; i" s8 X( Gcreate table tmp
8 W( g6 y. y% ]9 ?' V(
[id] [int] IDENTITY (1,1) NOT NULL,
5 X; j& ]' V' T* f+ J- k/ \: b[name] [nvarchar] (300) NOT NULL,
5 `9 G6 f- Q- p. F& Y[depth] [int] NOT NULL
5 v0 {9 q  ^& f$ A& [5 i);
, d& w8 t0 ?  g" q) O; d) h" ]/ M1 }
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
: j6 A" n9 L$ P" nset @root='f:\usr\' -- Start root
7 W, m8 ^5 I/ f! g2 n* n! \# ^2 \set @name='donggeer' -- directory to find
$ J/ S  k4 q; Y6 _7 ~- b! Oinsert into tmp exec master..xp_dirtree @root,0,0
$ n0 |' t! A/ f& F" y2 y9 ~set @id=(select top 1 id from tmp where name=@name)
) d! O" A. M8 B* c: x+ D  r# Q$ u( mset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
2 u' p  u1 l1 t" p9 Z) a+ Abegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
7 k, Q  B4 Q% [7 O( G: h8 zset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
select name from tmp where id=1