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

admin

查找文件的语句
, X+ {" A  j7 B: w6 \
CODE:

9 F$ l, w+ v; fdrop table tmp;
" f4 Y$ x: R1 @/ I. T; `create table tmp
& V' e7 j4 R8 Y/ q! p1 M4 }. I(
- y' W" Q" u7 h  S* u" B[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
. [; Z" p( S) z- F[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
' t8 ~: i: f/ \9 B- y2 z2 ^
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
" x) Z6 Z& C+ wset @name='cmd.exe'   -- Find file
: R* @8 ~! s) g% U* Tinsert into tmp exec master..xp_dirtree @root,0,1--
6 W$ ?3 `! Q5 O- i* H, ~, v. oset @id=(select top 1 id from tmp where isfile=1 and name=@name)
set @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
* q7 p0 ]+ @* l9 u4 @' \begin
8 ]# @3 ]. W; F( d3 u/ c6 a3 s2 iset @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
end
$ ]& h/ {1 E5 J4 b& M/ Dupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
, ]; a8 Y" a/ L6 c

CODE:
# x3 T+ n# w( u

% j$ G" {: X  n# Bdrop table tmp;
create table tmp
(
/ [& R: ~% g. z  b/ V% j[id] [int] IDENTITY (1,1) NOT NULL,
% y+ w- ]( ^& o& c$ g4 Q: R[name] [nvarchar] (300) NOT NULL,
/ `1 R# N' h3 T& K[depth] [int] NOT NULL
& b3 \5 B( a$ J/ W);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
4 D6 v+ A; N2 }$ r& i* Dset @root='f:\usr\' -- Start root
1 J1 d3 b! \/ ^1 u  I  B5 G" Dset @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
/ p9 s4 _& T& f, ^# Vset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
5 _( S, u; N! ]) w( ?while @depth<>1
/ N2 K* p/ N% e. K! lbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
* R' H4 K9 b' q2 ^0 ]9 Jset @depth=(select depth from tmp where id=@id)
% z/ y' [( y' y4 p6 lset @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