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

admin

查找文件的语句
- v$ l" T7 p* ~5 ], y# l2 a. ]. O: q# i
/ {% ^* A) C& F) ~CODE:

drop table tmp;
& E# ^. o; N/ b& N0 dcreate table tmp
(
$ n3 _: E# T* Y. C[id] [int] IDENTITY (1,1) NOT NULL,
0 C& {: F7 U! J- M* n+ H+ e[name] [nvarchar] (300) NOT NULL,
; f7 o+ i! Y4 s9 N( T+ }/ @[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
2 z# I+ V) _9 g# ~7 U, ainsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
9 @- v- P0 E- U+ u3 Z; p1 Q1 W2 yset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
  ?+ [% }# I$ _  G/ ~) u/ B* |" bwhile @depth<>1
/ ^# Q1 L. W# H1 E. t) x' Lbegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
! s3 k! r. W7 X. B2 Eset @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
: M7 y* [4 J. {& t( K4 kselect name from tmp where id=1
4 s4 Q3 ]$ b+ R% b# b8 c
7 ^% ^0 j, G1 L1 a; s) ]查找目录的语句
  }7 t+ P  H- q" C( d, o
/ B* d/ `* q% `6 P/ P
CODE:
- r! B+ b2 r5 X0 D5 p8 M( p: q

drop table tmp;
create table tmp
  @4 V( }! Z5 I(
5 a' J& l; d  n8 t5 B[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
* D) J  ^' s, F+ i);
$ ]- v: P- p+ z% o
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
+ T( X+ M+ Y. Uset @root='f:\usr\' -- Start root
+ {$ Y# X2 p% X) w( Xset @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)
+ U% t# ?: r# i/ R. uset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
$ G7 E3 {( ?: [& B. G- tset @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
0 r; N* y% b; Fselect name from tmp where id=1