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

admin

查找文件的语句

+ ?: L6 S5 H* S; y5 v/ c2 T: n3 ?7 [CODE:
" y: x3 D" s4 `0 B, h9 f- F
% k% G( v6 l9 H0 F) Wdrop table tmp;
create table tmp
(
) \- M7 |1 v# P7 n1 F3 \, a[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
) ]6 Q1 y, s5 n0 F[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);

% X* J2 b- e- k2 |* ^3 H8 Cdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
+ K3 I4 I# h8 U/ K7 p8 aset @root='f:\usr\' -- Start root
; _" |( {: e/ f' Aset @name='cmd.exe'   -- Find file
) E4 k" q( \4 n3 s% l3 }$ o% iinsert into tmp exec master..xp_dirtree @root,0,1--
set @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)
- H  X! T) ~6 V3 o! u. H0 rwhile @depth<>1
begin
$ l0 D5 }$ [, f8 [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
% T8 A: ?* ]/ i8 |/ Mend
  t2 z5 V5 _, _, Tupdate tmp set name=@root+@name where id=1
* \" y9 n8 t. r* rselect name from tmp where id=1

查找目录的语句


CODE:


- ]3 B+ I& L' t* {! `' c) ^1 Vdrop table tmp;
4 t4 i- G- ]* U) v* L4 ocreate table tmp
9 v3 X* i( C; |4 I  ?3 P" a, O(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
# _$ Z: I0 S3 W3 f- B8 F[depth] [int] NOT NULL
, y/ ~+ j# y5 p- B3 b3 ?2 O$ a);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
set @id=(select top 1 id from tmp where name=@name)
, h+ B; m% ^3 D( d1 ]set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
" v9 G  f. \3 t9 o+ @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)
set @name=(select name from tmp where id=@id)+'\'+@name
: E1 `/ c' X' N4 t0 o- Q! Y) @end update tmp set name=@root+@name where id=1
" x6 w+ T# f3 g/ v0 n; k- Lselect name from tmp where id=1