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

admin

查找文件的语句

  G" O% y* V2 _: s, u% eCODE:

drop table tmp;
' a* X0 }4 |+ Vcreate table tmp
3 @% [# e4 L0 U) }5 s1 W( a(
$ F* d5 d: L. z- ^0 N1 N[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
2 Z) [! Y6 m$ [! E# K; ~[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
1 N0 R; p/ G, X: V7 z# C1 Gset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
2 o' O: o% _2 oinsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
- y. g6 I# i/ N. b/ i  Qset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
: N9 j0 D2 _; S3 z, S/ }2 J& {begin
. E7 Y8 N+ b1 S2 p: P8 a. Oset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
+ G4 C# \( f6 _1 P" j8 j5 wset @depth=(select depth from tmp where id=@id)
, ^) [1 S& }& [: n) dset @name=(select name from tmp where id=@id)+'\'+@name
: U' A, H) ]* V$ I/ O" t6 z# D. Q7 c  c' Aend
update tmp set name=@root+@name where id=1
select name from tmp where id=1

/ X! }/ {1 c9 Y查找目录的语句

7 G' E) B; H: T' z  y# W8 C
CODE:
: h# I; W1 k4 a% D7 Z# _( I
( |; z3 E7 p% x9 @2 B
7 z0 ?8 ?! U9 Qdrop table tmp;
6 `0 F; @7 h2 Y. @/ X5 j: u0 Q- W7 [create table tmp
0 j  M' q4 l) _- I5 e2 r* X8 d(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
2 d3 D/ T2 Q3 C/ Z' |4 u) c# _/ A$ @);
# l- R  n1 H" ?7 Y$ \( _
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
) `6 G  G3 X9 u1 D% Ninsert 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)
while @depth<>1
begin
set @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)
+ {  M* b- {7 v' M) cset @name=(select name from tmp where id=@id)+'\'+@name
- y; m* y: _4 U+ ^# u! |end update tmp set name=@root+@name where id=1
select name from tmp where id=1