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

admin

查找文件的语句
% B  i  ?5 E, }" e8 Z3 A
: e3 h  C( P, K+ @CODE:

' Q9 g: n& d. @' wdrop table tmp;
- p( _5 P0 j/ ?6 w; T; ?% d" Z) {create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
$ W+ y9 m0 I* ]& M( X" P; q[name] [nvarchar] (300) NOT NULL,
. ^; z/ ?$ Q: @$ o! ]: `8 k[depth] [int] NOT NULL,
  q! {0 W4 Y2 B0 P[isfile] [nvarchar] (50) NULL
);

0 B, x  f% A0 g7 n  }1 G3 f: {declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
/ H2 x& b1 K6 n6 ^set @root='f:\usr\' -- Start root
set @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)
) g* M; Y) p7 A# R' |while @depth<>1
begin
# k# S7 d5 E7 o; B' Iset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
7 Q: c( @' c. w' S$ B7 Pset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
( b( z. z( V6 m+ q! m. U$ Pend
update tmp set name=@root+@name where id=1
2 B: I: }4 t$ Aselect name from tmp where id=1

/ K* U) D. `9 i9 H2 z9 V  R) Z) N查找目录的语句

0 n' \. ?7 i! h  k
CODE:


8 D& X4 w: m) D$ j8 Mdrop table tmp;
create table tmp
% g7 r. d% n' i' y7 g  v$ M% Q(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
5 I. E$ P/ |2 k- }9 U+ g. Q! Mset @root='f:\usr\' -- Start root
) k( i, {6 k- U  M' G9 ~* G4 Y. uset @name='donggeer' -- directory to find
& C. k) D; Y( e  d$ J& l9 Z$ H5 oinsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
4 o" o% U9 x  z' }" j0 Wbegin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
! T( P; f1 |2 n; q. r  R0 t/ |set @depth=(select depth from tmp where id=@id)
  E/ w" O1 Y" S' \4 Q/ x. P3 kset @name=(select name from tmp where id=@id)+'\'+@name
5 S: ~* r! [& b: [: Nend update tmp set name=@root+@name where id=1
select name from tmp where id=1