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

admin

查找文件的语句

0 V" j! |. W+ b" D/ SCODE:
' ]7 g; \* e7 K& Z  e
drop table tmp;
create table tmp
; ~* P& f: ~& |8 B8 t: i(
4 E; g, g; Y, V- Z: I: m[id] [int] IDENTITY (1,1) NOT NULL,
8 j" X5 m, Z6 h" w+ S[name] [nvarchar] (300) NOT NULL,
7 Y+ J; V8 X0 u0 @7 L: ?! l( _- g[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
+ f3 V9 u! E( z5 ?* ]- d! F' Jset @root='f:\usr\' -- Start root
! Y0 {5 J9 ]5 `  \6 q8 Z% rset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
, f/ v- P9 M5 k5 k: n% P% Eset @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)
, J1 S/ M5 [% m( a( g3 Pwhile @depth<>1
- X1 g4 S: {! @1 T( z/ r" abegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
& p$ B4 u( D5 V% B$ c7 [+ \set @depth=(select depth from tmp where id=@id)
/ b* l+ ?6 I. X* tset @name=(select name from tmp where id=@id)+'\'+@name
' h5 f3 G: U+ c/ v* Iend
' Y6 m0 s8 X7 q# w3 U5 r. Q% V/ v+ _update tmp set name=@root+@name where id=1
select name from tmp where id=1
: x( L* F9 K2 w" Z. V
, j% L1 e; g* q1 p查找目录的语句


CODE:
2 D7 D* Q! N% t% `, e9 ^" q

6 [. W) a9 E; U9 l5 n# W! r* i# F+ udrop table tmp;
0 d9 O0 M8 I( n3 Gcreate table tmp
(
+ A, @" {/ f* g" S[id] [int] IDENTITY (1,1) NOT NULL,
" ]3 v! G& {- ?  c; p[name] [nvarchar] (300) NOT NULL,
* ?) T7 K7 F2 ^3 K2 Q' \3 S[depth] [int] NOT NULL
1 }1 t2 [; s1 |0 V. `+ \);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
$ r8 b& Q4 T5 g( s7 D, z- xset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
4 C" z" U' j) D) P9 m1 P+ Zset @id=(select top 1 id from tmp where name=@name)
set @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)
$ _. N. |/ q- x. R" q$ pset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
' B* V6 J7 z4 m( }5 l# G4 ^end update tmp set name=@root+@name where id=1
5 T6 Y: ?4 f" B4 v$ pselect name from tmp where id=1