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

admin

查找文件的语句

CODE:
$ |7 ~7 W4 @' Y- k/ B7 @) C; P
6 z/ C% |% _' F+ Q9 s6 {0 W/ R' Qdrop table tmp;
create table tmp
4 w4 T, _/ N! t" x8 ~& K(
1 @1 x0 ?  x6 M9 `- a8 W[id] [int] IDENTITY (1,1) NOT NULL,
# |! b, f0 A2 }9 [9 d[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
  @( F: Z9 |; s7 c7 C);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
; Y0 m! {1 b4 X6 I: Yset @root='f:\usr\' -- Start root
: a, P3 I# N* T* W* {+ L! O' Aset @name='cmd.exe'   -- Find file
insert into tmp exec master..xp_dirtree @root,0,1--
: Q9 u' B+ d3 o3 qset @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)
# w4 l" c( ]1 B, p1 hwhile @depth<>1
* a) P3 [- ^8 J; jbegin
set @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)
set @name=(select name from tmp where id=@id)+'\'+@name
end
- ]: |7 k. b: o) q# {8 N$ t0 Dupdate tmp set name=@root+@name where id=1
8 K* s; g% h. n* Bselect name from tmp where id=1
9 d' L$ T5 f9 V% t+ T; m7 b& a  f- {8 f
查找目录的语句
7 i1 s$ Q  @* w+ j& }& v6 r

9 `+ B% v. u) z7 r3 ~4 V: PCODE:
8 F4 i0 i+ \( [' r
( p, u) R5 W& I5 v" v
drop table tmp;
create table tmp
& }2 g; \+ _# Z/ ]5 V) ~(
0 V) G+ T* H) g[id] [int] IDENTITY (1,1) NOT NULL,
1 T" H# s" P* n/ [* m[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
1 A1 l, _" W# \  G3 z/ j);
. a( D: H$ N: V% h2 M
3 x6 `; k+ S7 {9 e9 I, Z; fdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
# P7 u& s) _# m  N, Rset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
- O6 @0 K6 \; E  K; Rinsert 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)
8 T% D& K$ n' R. Z( v5 v1 Swhile @depth<>1
begin
. Z* ?  u4 C# d# t1 G0 I6 ^% dset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
$ D4 b  ~- Q: ^" F" ?7 I) fset @depth=(select depth from tmp where id=@id)
- c- L0 m( n1 Pset @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