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

admin

查找文件的语句

* `* z( L/ s$ C0 o8 i: wCODE:

6 l2 I5 p. R, x: A- vdrop table tmp;
3 |2 c) j$ B# U' s" w3 ]7 l" [create table tmp
4 _. y* {% I5 c+ |7 K(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
" ]% P, C1 C- k7 o[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
, o) M8 r( J& p5 f);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
3 g- ~) q* j. i  {( ~# o* g8 {set @name='cmd.exe'   -- Find file
$ J! u6 S5 z$ W9 `0 q4 [6 n: \insert into tmp exec master..xp_dirtree @root,0,1--
( X; O* p$ {: b2 ?/ iset @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)
3 X0 `4 z% d$ n9 s3 |  a) O' ywhile @depth<>1
begin
. p- [  I" w% i) J6 fset @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)
1 d" A, v/ R2 }- Y* c2 Gset @name=(select name from tmp where id=@id)+'\'+@name
9 o2 I( B/ T( x0 M- C0 aend
9 k! D0 a5 W* B6 e/ mupdate tmp set name=@root+@name where id=1
8 `' U: U* ~9 O, H* eselect name from tmp where id=1

' ?; R/ }/ S' f: a+ ~查找目录的语句

; w& g1 k  d. G& a
0 t: ~: ~# q/ v* z# c! }. `( }CODE:
, L* m" x, F: x' w* T
4 f1 y: Z$ ~/ D/ x2 {4 Z: Y
# q! [' |0 r- B- Jdrop table tmp;
create table tmp
+ ?# @( o6 T& C0 }2 q- t+ O! S; `, A(
[id] [int] IDENTITY (1,1) NOT NULL,
- r, c6 a( c2 v[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
6 O. k  y) K% K% W% s);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
4 A( l- `3 z! g1 }1 x2 S/ cinsert into tmp exec master..xp_dirtree @root,0,0
$ ?8 S, E$ z! G- w# \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
* O% @8 m6 v, ^! S' sbegin
2 j7 a6 E1 M# aset @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)
set @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
( q+ R/ j( k( A; P" c3 ~, [select name from tmp where id=1