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

admin

查找文件的语句

2 s/ z  j8 U& w% g/ j3 ?CODE:
6 [; r- S6 |9 }$ D+ C0 L1 |
8 n0 l. H3 W4 O; P8 ~. E! Q5 `. J: xdrop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
' b( |1 a) G! ^' A* d[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
8 ?. w6 g) n7 \) N6 [9 D% ^
0 R- t3 D# I' f2 |declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
3 w5 r: _" r7 O' c9 y( z( jset @root='f:\usr\' -- Start root
) }" n, M* O  c3 A7 Iset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
7 X! ~5 [9 e/ ~# pset @id=(select top 1 id from tmp where isfile=1 and name=@name)
7 o1 q' z( @( lset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
/ Y2 ^: _" N) w' b+ ?! e2 K0 `0 B# W# wwhile @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)
6 f1 }! V- [! s3 c) ~set @depth=(select depth from tmp where id=@id)
( f& r4 P! g. {" a. g- L3 dset @name=(select name from tmp where id=@id)+'\'+@name
. A+ N7 g, S& M8 y/ eend
update tmp set name=@root+@name where id=1
select name from tmp where id=1
% c. k  |( k  e8 H* M
查找目录的语句

5 F: o0 m2 G$ k2 o8 {
0 g: D. _! [/ r& T8 Q% u$ wCODE:


+ s6 a( ~! S( V' j/ `drop table tmp;
create table tmp
(
2 K4 Q" d1 W( n$ \. v7 m) J[id] [int] IDENTITY (1,1) NOT NULL,
/ U2 I) J4 z+ }% P# X" b: l& Z" e[name] [nvarchar] (300) NOT NULL,
( l6 W1 {2 c4 j! m  I' ~% A[depth] [int] NOT NULL
9 P0 Y" v( A) v1 I, a) T8 p; @$ T);
) M( u( R# D( p! |. l  l; X
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
2 X& `( S  I$ l4 Kset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
set @depth=(select depth from tmp where id=@id)
, U5 j6 J! j3 p+ |$ Sset @name=(select name from tmp where id=@id)+'\'+@name
, f$ C  z2 v5 d1 T4 M- ]end update tmp set name=@root+@name where id=1
( w: u$ d' r% M5 s8 L# |select name from tmp where id=1