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

admin

查找文件的语句
2 i) N6 p) c' `* R3 C3 A* O* W/ M
CODE:

drop table tmp;
9 v, J  }0 R: J' rcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
: w7 w7 u8 J) O  U9 C" I4 z[isfile] [nvarchar] (50) NULL
* K; O* i) `4 j* x( @$ S* q2 P);
8 |! R" e- `) y) P! y: X
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)
3 v8 l: Z# o& K7 i7 E% Mset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
9 Z" ?# T, [% Vwhile @depth<>1
begin
7 J# j4 Z2 i" L6 Gset @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
! D8 F) l0 K; U7 Oend
& `. d6 X( C0 g( K+ ?* y% k, y/ I4 ?( qupdate tmp set name=@root+@name where id=1
' `" W! N  @# Y  Dselect name from tmp where id=1
% o' [( j# c# K2 g) \4 o
查找目录的语句


CODE:
0 e, \2 R5 E& c: ?

drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
+ k0 C- M3 n) l7 e7 `% Y* Y  W[name] [nvarchar] (300) NOT NULL,
: v5 j& p6 ~9 B) u  }0 o: x[depth] [int] NOT NULL
; s9 C9 T6 A6 x. Y$ X6 N' q);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
1 i2 R% |6 U& y' w! W% Sset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
+ L; b6 W  i( H  n. r# y: K: eset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
% e3 ?' n# M, q% ^2 j& x( s& O7 {while @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
6 `4 r2 Z) [! |! `0 A( bset @depth=(select depth from tmp where id=@id)
" c9 @1 }6 N: [8 i6 s6 P9 q- zset @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