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

admin

查找文件的语句

: B2 A6 T1 g5 X) _CODE:

# |6 i3 W1 A% X' G  tdrop table tmp;
create table tmp
1 `' ?. o! m4 z, y9 J; P(
[id] [int] IDENTITY (1,1) NOT NULL,
. U' q3 B+ r0 J* j[name] [nvarchar] (300) NOT NULL,
2 I9 a1 B6 S2 j& a6 |  v0 n+ V" A6 n[depth] [int] NOT NULL,
3 @: P9 @3 k( t" a. a[isfile] [nvarchar] (50) NULL
);

8 P, D2 N# M; m3 i" {declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
- D# A9 U  }. d) s- l. Sset @root='f:\usr\' -- Start root
3 k  f/ {+ S& g: ]set @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
  s" H3 o7 k8 S8 |: lset @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)
while @depth<>1
+ L% Z1 y9 f+ Vbegin
set @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
7 [8 [) I, z3 }' S  t; _$ p/ v# nset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
3 Z, t7 S4 T- i2 Y3 ^$ m" X; k' Hend
  I8 m: [7 b5 {7 n+ cupdate tmp set name=@root+@name where id=1
select name from tmp where id=1
- \# _/ _8 o" |, I2 o  E/ C* B+ Q: u
查找目录的语句


CODE:

. q% h* p: w9 b$ t* r) T
( T9 i& o6 s  h; ~( Rdrop table tmp;
+ m& ^+ s+ L, d  L* h. {3 acreate table tmp
(
) c1 }: t  I, X* c! m( a, t$ a' \[id] [int] IDENTITY (1,1) NOT NULL,
6 ]! s1 \* d+ G[name] [nvarchar] (300) NOT NULL,
( N* s5 ^9 x2 Z+ F5 o[depth] [int] NOT NULL
6 W: P$ t+ k7 P5 @);
' M4 I4 @9 p: x9 ^
; g* x; Y( X! B# V) v1 H" mdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
. [; T, J$ Y- pset @root='f:\usr\' -- Start root
8 n5 z- S/ Q" t/ {set @name='donggeer' -- directory to find
; g1 V1 w9 T: g7 Q+ Linsert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
7 \' o3 h* b- _* ]1 K5 qwhile @depth<>1
begin
set @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
4 y& }* g+ Z/ v. K1 kset @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