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

admin

查找文件的语句
) n! K, T; T& P
CODE:
% e% T! l$ D7 ]: r7 R
drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
* {7 o8 p  L# t# d( d[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
7 y( U, y# V* V! ?, z);

' Z9 {9 V2 e1 `% {5 \9 pdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
! h% @5 B3 V+ x+ d- Wset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
. m% Z" B5 P# Y5 h; C! u; C( u* Q" ^, e8 gset @id=(select top 1 id from tmp where isfile=1 and name=@name)
* }% A" R! C' i7 ^( T) Q- Y' mset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
8 X/ @/ \5 M- w7 {7 y# uwhile @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)
- k% l5 m5 t1 U/ A4 O* B, zset @depth=(select depth from tmp where id=@id)
' c0 ~( `' ]4 `( _: wset @name=(select name from tmp where id=@id)+'\'+@name
end
! g: v# Q' @3 Z$ Q7 y- q4 _update tmp set name=@root+@name where id=1
select name from tmp where id=1
9 `2 d& M! W9 i& |7 k  v6 E
查找目录的语句
2 F) E! h$ e- O5 H6 K

( c6 n$ f- {7 }- F, L" SCODE:
( S  X# t  F# `! r4 h- x% X

drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

$ ~& W: j0 T4 U4 ]$ B" F; {declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
1 |+ d" S& k+ @8 P+ v5 yset @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)
set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
" S3 k4 E& u- O& `/ N5 s1 n7 kbegin
2 R) h5 }) ?; v5 V3 j; [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)
5 A7 m7 r+ n, @* Rset @name=(select name from tmp where id=@id)+'\'+@name
: ^4 P1 `, Q1 ^3 K4 s7 fend update tmp set name=@root+@name where id=1
+ Y' W" n5 F+ Q: s1 U; j6 Mselect name from tmp where id=1