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

admin

查找文件的语句
% l1 S) K: a) M) I, S$ ?; z
CODE:
3 k1 S  M! ]6 B$ I
* G2 Z9 I4 w# R2 @: W- Bdrop table tmp;
create table tmp
! s7 w! w! n# X& \) g% u* C& H% s(
* C. h" U! y$ X; d. y& F, p1 e$ O& }) s[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
2 c& I/ g6 Q9 Q: D[depth] [int] NOT NULL,
* e0 w$ _1 Y/ M4 x$ M[isfile] [nvarchar] (50) NULL
);
( s9 k# ~  t2 Z6 w# \1 }! b: ]
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
9 h6 e2 c3 b- U! ]$ A+ [6 Pset @name='cmd.exe'   -- Find file
insert 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)
while @depth<>1
begin
5 Y& @5 `1 A+ n) l7 C3 p- p& sset @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)
! c( l$ W9 \" N2 z4 i. s/ Lset @name=(select name from tmp where id=@id)+'\'+@name
* f# s- F8 g" B/ @end
update tmp set name=@root+@name where id=1
# Q% n& x9 D# M& E' [8 ~& {2 C! |select name from tmp where id=1
! K  d# t- o* w3 z( j
查找目录的语句
4 X6 w- B& s7 H, E: h' U5 G

CODE:


, H  O# Q  ?; j! O$ gdrop table tmp;
create table tmp
$ ~0 m% T. g/ s0 L0 _3 y' y(
2 F: M0 _" o3 J; \[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
8 E* `5 k) N: d/ `, X1 g; S);
, y& p- i; }; K9 `  a  y
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
+ J7 X- @5 l& h# W% H! t! n: J; ?set @root='f:\usr\' -- Start root
$ P$ m; W& i8 Fset @name='donggeer' -- directory to find
, C6 a0 |7 q, m: K6 _insert into tmp exec master..xp_dirtree @root,0,0
' q, ?; _+ y. O% S& ]+ I' Q/ Q0 zset @id=(select top 1 id from tmp where name=@name)
: f/ i0 A/ ~' ?* Mset @depth=(select top 1 depth from tmp where name=@name)
, T; S5 E5 A/ qwhile @depth<>1
) b& X' o$ G+ {; Z9 nbegin
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)
" X" z+ \4 ?8 R8 B& ~, s/ T5 Rset @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
+ V1 Z! s& F" X& ]& l$ `select name from tmp where id=1