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

admin

查找文件的语句
6 B+ H8 u! o4 j3 R/ ]
CODE:

5 A! ?  Q  N# H( M+ F. S+ _drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
- {9 Q4 O7 W" Z. j[name] [nvarchar] (300) NOT NULL,
" L' c% u: j, r[depth] [int] NOT NULL,
9 i6 X* o: L/ x! F, y[isfile] [nvarchar] (50) NULL
);
' k; ~' C5 t" V) Q" x
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
% Z* w' k# n! q. \  ~+ C; Cset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
! K* C( E$ s+ W6 S* k; Ninsert into tmp exec master..xp_dirtree @root,0,1--
3 j6 A5 F. Y1 S. v5 C$ mset @id=(select top 1 id from tmp where isfile=1 and name=@name)
* y$ y6 [" }1 p2 g+ X3 bset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
3 a2 ]  y' a+ S% {& B8 ?( f8 ~9 Gbegin
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
- D6 g, f6 B! _; S7 k$ ]; p$ nend
2 J) P6 V# U  O9 g2 e. R; {update tmp set name=@root+@name where id=1
select name from tmp where id=1
  _9 o: ^, N* I/ i- q5 ^  o
查找目录的语句


5 C! Y0 I3 O6 C( T8 j/ f) H/ zCODE:


drop table tmp;
. q% H5 H7 J3 _1 dcreate table tmp
- D; I7 `/ P" N7 |  J  i(
[id] [int] IDENTITY (1,1) NOT NULL,
& c+ H2 j5 s) _. ^# u- r$ q) G[name] [nvarchar] (300) NOT NULL,
/ ^6 _- b* c4 E6 ?+ E" x+ b[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
+ i/ ~" L3 W8 n3 Qset @root='f:\usr\' -- Start root
) T# P: M# D4 `, W6 Tset @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)
& O) u% a4 t& U6 I: mset @depth=(select top 1 depth from tmp where name=@name)
. k4 C% ~2 n) u2 Gwhile @depth<>1
begin
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)
  m0 q$ k) j# j8 b# Rset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
9 F) z+ }6 S6 L& C+ Yselect name from tmp where id=1