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

admin

查找文件的语句
3 ]. P' m4 U3 E$ W" y' o/ n
CODE:

- N' J& e" s1 K6 e. @' wdrop table tmp;
create table tmp
% L- V4 _% }) o: V2 K(
[id] [int] IDENTITY (1,1) NOT NULL,
6 S8 w, Q3 J& ^& {! r) O0 t- U[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
/ M5 |1 Q. U2 c[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
) r6 S/ R: g% R/ w  k+ }2 Dset @root='f:\usr\' -- Start root
) O' x8 \6 ^' Q; hset @name='cmd.exe'   -- Find file
# U% A! Q' P/ c% b8 d* Minsert into tmp exec master..xp_dirtree @root,0,1--
" l+ R- V( }8 q+ D7 I$ P5 ~8 X7 fset @id=(select top 1 id from tmp where isfile=1 and name=@name)
: ^& f# O) z+ ^2 [* 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)
set @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
0 S* D8 e) X8 d( h# @: k/ X# r# T* rend
update tmp set name=@root+@name where id=1
7 r0 C! H7 H: v6 ~- H5 A, c4 s3 vselect name from tmp where id=1

查找目录的语句
' U5 S% M( f$ E8 j$ q3 k- ^

- f# F8 b, X0 W% x4 Y' O( \CODE:
7 V% D$ m# T3 a3 r' o
; W% j3 A; _( R- S2 l3 L( C
drop table tmp;
create table tmp
(
5 y4 @. f- a0 ], C7 E[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
5 I- B# K9 C1 ][depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
0 r  L! J# l: P; H9 yset @name='donggeer' -- directory to find
1 p4 b4 a0 A- `2 T. P# ^  M! xinsert into tmp exec master..xp_dirtree @root,0,0
( b7 M9 }% s* t) Z+ ~- P: E$ V( iset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
7 _8 i+ T  C7 W, L5 V7 jwhile @depth<>1
* C$ U, r# {" R0 l+ S7 Wbegin
) i  D6 U6 h5 H% i5 z- Lset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
5 ?8 c& ~( O/ y" [; ~set @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
0 q. Y! b% [, Q: Eend update tmp set name=@root+@name where id=1
select name from tmp where id=1