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

admin

查找文件的语句
. M  Z- N2 [' U& O/ c
CODE:
- Q0 b7 j; I- Y' ^) Z
drop table tmp;
9 z5 N( S7 X" qcreate table tmp
- O4 b" W1 ^1 R) ]- S, `) V(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
- D; x1 C8 \  Q1 ], W);
# t8 }& S1 B4 b# g/ W
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--
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)
# v  r; f$ m; j% j: lwhile @depth<>1
1 T, R9 M: U* e6 m& _- }9 E; Cbegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
! P% X4 U/ D' |0 I6 ?* vset @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
select name from tmp where id=1
5 x6 m1 ^! x+ z! `
查找目录的语句
* ^. }9 b- |: x! m, B+ E0 C- |
, I9 E! I0 U$ Q& @5 f/ V
  O0 _7 f9 t; k; I( Y* SCODE:
+ n3 g) p. h. @# z% I' e

drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
9 g% w: ~0 K) C[name] [nvarchar] (300) NOT NULL,
; R9 E, X" D7 T# u* E% i[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
9 S0 C# ^2 }) D2 u& @, tset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
, W5 p" o; }) ]& H9 ?set @id=(select top 1 id from tmp where name=@name)
' g4 p; s9 d3 y+ p6 Y% H" Eset @depth=(select top 1 depth from tmp where name=@name)
' S+ C  _9 |$ _, o4 Swhile @depth<>1
begin
7 x$ ]( @* y7 @6 t' h/ ]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)
6 f: u3 Y0 S' q" Cset @name=(select name from tmp where id=@id)+'\'+@name
4 p- g7 f- [* v8 [7 D0 O! Uend update tmp set name=@root+@name where id=1
select name from tmp where id=1