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

admin

查找文件的语句
# x. v7 K. l) V
6 ^7 w) |# H. \  b2 UCODE:
& k7 ^8 f( [8 k0 V
  B3 p) L4 M0 G$ h1 |: Jdrop table tmp;
/ f5 {/ f0 c: ^1 ]6 d( y; }create table tmp
+ C$ ?$ [+ K8 s" t0 c* X(
5 y+ ^) Z  {4 u. J7 g[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
4 ^( v7 I+ `% {  c3 L8 g: O/ E( L);
- S6 ~, T4 ~, S9 f" p7 N( w
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
. I$ j6 p. ]7 C% M9 L5 z6 C$ K+ zinsert into tmp exec master..xp_dirtree @root,0,1--
set @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)
" ?+ h- g& `* f5 _' k" ^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
end
update tmp set name=@root+@name where id=1
. W; q; X& e3 m; e% q# r/ xselect name from tmp where id=1

查找目录的语句
, v5 W5 S/ D  ]9 q* T8 x2 g" V9 ^& \5 ]

CODE:

, @2 V' V3 D# O+ y2 c
' l/ p5 p( i+ \7 a: o5 H' e4 gdrop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
3 K( g% ?1 k3 ^9 }8 }6 x[depth] [int] NOT NULL
);
" j7 W0 q: F/ }& {* V
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
# e5 L' B. n! r' ~, C+ |$ Q  p# Jset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
, n  N1 b8 P! M& m% G1 P( j9 D  uinsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
& C1 ?/ F: z+ U$ S$ g6 z- }set @depth=(select top 1 depth from tmp where name=@name)
0 K/ a8 ]  W1 |. ]' n# g9 b( A5 wwhile @depth<>1
( g8 G8 |, h& [$ c4 Q. j# r/ O. gbegin
" g9 U* V3 S" P0 I1 Eset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
. H0 n4 }( X4 fset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
1 _& b) J* B9 h( i' \: R6 `end update tmp set name=@root+@name where id=1
3 T" a" X: _4 w6 g8 d/ _" B2 Yselect name from tmp where id=1