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

admin

查找文件的语句
, ~& G% {0 s- t- s/ q4 X$ t
CODE:

0 d2 U* {3 Q3 Q. T$ a) |drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
8 b/ {0 H; J+ _$ _! L& Y; \[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
: W5 ^9 r* J; w3 l; D! lset @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)
5 A$ U& B7 a6 h4 ^6 Nwhile @depth<>1
4 y5 p0 j3 Z$ X! [; ybegin
. K* ~- m; B' t& G# U0 n" R+ Hset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
0 @+ X, m: X" M) g; Sset @depth=(select depth from tmp where id=@id)
8 Q5 o+ P2 ~$ g( v: h' `4 cset @name=(select name from tmp where id=@id)+'\'+@name
4 |( H% n$ c: P/ K, y2 \+ _$ ~( [4 Lend
update tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
7 P9 }  Q0 [3 }

CODE:


2 \5 d* K; {3 v4 D4 Gdrop table tmp;
1 A; D; u; g: Qcreate table tmp
" }9 V7 c' Y8 \' J" c6 A0 p(
) k/ Z/ A4 ~0 y! k& o9 B[id] [int] IDENTITY (1,1) NOT NULL,
/ r9 g) d7 ~; ^7 F: q* q. p0 e, x[name] [nvarchar] (300) NOT NULL,
9 m# t8 i5 T2 k: I7 |; w6 z: p[depth] [int] NOT NULL
" X4 O% d4 O  X. X9 |);

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
9 a( m  |2 `4 ~, m5 L" X! |& \" @+ dset @id=(select top 1 id from tmp where name=@name)
7 O* g; _8 I* u* _9 Rset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
+ I2 Q  A4 A+ w/ L6 M5 }; ^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)
+ T  p( l/ J/ Q: T0 Sset @name=(select name from tmp where id=@id)+'\'+@name
0 W1 @+ o  V  X' w$ q% U% Tend update tmp set name=@root+@name where id=1
; W, W* m: f7 ~6 e0 Tselect name from tmp where id=1