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

admin

查找文件的语句

6 A2 O8 C5 v. t" M( HCODE:
! h* K: Z' h% F3 r9 w6 a  O
) O: C6 l6 ^( o0 C& idrop table tmp;
create table tmp
(
2 _' u- c( m9 b2 E, E) A[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
) f5 X5 b, m+ ]& X0 h2 H, s9 y[isfile] [nvarchar] (50) NULL
( P; X8 k1 ^* v);
- v- j6 J4 [+ T7 J1 w1 \; d: G
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--
/ ^5 ?- a9 r# e' Aset @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)
. O6 O7 t, X; ~) r6 n- [9 E: nwhile @depth<>1
+ D: \( p9 L1 e4 Wbegin
! u+ S& c/ ?  Y# j$ ~set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
" Q, R' `0 k) F( B0 ^1 M+ Rset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
! F) \4 ?$ m% u& [+ Wend
' t4 `' l4 C# f8 mupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句

2 g- G' @" u& ^
CODE:

( Y; _. V# v* Q4 g& @0 p8 p0 P! N4 H
$ g. c5 m1 O* }  |" ?0 z! kdrop table tmp;
* ?4 u- v/ T6 Y  q8 Qcreate table tmp
% F1 l" b1 K. X/ c(
[id] [int] IDENTITY (1,1) NOT NULL,
7 K' ~: F" \8 w1 F" h[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
3 @/ [9 F" q) C. S: Q( L; b+ v# }5 \- j);
0 M! c) R1 d# Q( v" I
/ R/ U: A: `1 p& R4 vdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
- N3 O4 L- u" o( X- u0 |# i# x& Nset @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)
5 L) C5 O# V  k) T) uset @depth=(select top 1 depth from tmp where name=@name)
$ f9 e" H' }& ]4 _) ywhile @depth<>1
0 \4 C- W1 ^+ l* gbegin
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)
set @name=(select name from tmp where id=@id)+'\'+@name
! p7 f% _1 ?* H5 q7 N% aend update tmp set name=@root+@name where id=1
select name from tmp where id=1