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

admin

查找文件的语句

9 J0 P% W. c1 S3 d9 L) T6 {/ fCODE:
% U' j, m1 |# K* n3 [6 y
1 f4 j3 J1 u# h+ Gdrop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
0 \& D/ O5 c: a0 O& R[name] [nvarchar] (300) NOT NULL,
3 ?/ U0 R2 q+ R' Q- ~8 Y[depth] [int] NOT NULL,
5 u6 a- q& A. x$ m- E" K3 l0 T1 I[isfile] [nvarchar] (50) NULL
);

( I: Q3 D! H( K* X5 C  ^0 Ddeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
* N& J! R8 R) ?& C' W  yinsert into tmp exec master..xp_dirtree @root,0,1--
1 ]1 E6 n  U: c- B! Pset @id=(select top 1 id from tmp where isfile=1 and name=@name)
3 g: z, B3 B5 Bset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
3 b; f9 L- {+ Q1 O. {( O* a: ]- Bwhile @depth<>1
begin
; M* i( z5 Y" z% v+ R1 _set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
set @depth=(select depth from tmp where id=@id)
7 u" ?& J1 g7 ?* Y% [+ Jset @name=(select name from tmp where id=@id)+'\'+@name
end
4 Z8 ^" }! q- ~0 lupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
8 l: u! D8 U/ v; Q6 M

9 c% x( q; k' OCODE:
/ Q( N' H# O+ ]3 U5 S1 g" E

drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
5 o4 Q0 d) D7 P/ w+ n! v[name] [nvarchar] (300) NOT NULL,
- b" e5 G% {4 ~) d* c; y0 ][depth] [int] NOT NULL
);

: b5 C+ K9 q! D  J* g/ t2 Ideclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
2 Q. H8 M; C/ Q* ~& Mset @root='f:\usr\' -- Start root
5 m# g+ ]& A' j/ x0 sset @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
% R. c$ S% }& U4 Y8 n1 J4 c; _6 jset @id=(select top 1 id from tmp where name=@name)
: _; E& J5 T7 ?# bset @depth=(select top 1 depth from tmp where name=@name)
( |% N% H. |6 G; _# U( awhile @depth<>1
" H$ {7 ^( C5 ?# d" P1 kbegin
! n# E: u. L5 D4 k: w- Z' \set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
8 u" y2 }( c: b; V. B2 Y" @8 z0 g6 O  {set @depth=(select depth from tmp where id=@id)
- O' Q5 J; g) J" Dset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
7 f' f' }- j9 C: Kselect name from tmp where id=1