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

admin

查找文件的语句

CODE:

drop table tmp;
% }9 y9 F. W# W1 E3 ^0 {$ {3 m4 qcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
" U2 d( M% e2 M2 X4 l/ [[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
8 `( l- [4 g; K' S* |  Z1 q
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
* y" y  U# Y# v, ?' x$ l# vset @root='f:\usr\' -- Start root
1 }: _& n% w& D7 P# Jset @name='cmd.exe'   -- Find file
insert 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)
while @depth<>1
begin
8 ?- u3 r( i& Y' Z3 k" Eset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
# S. p; L! h' S6 o5 R: \$ A+ uset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
) O% K  _& V! l: ~9 G# j' P' yend
7 r* e, P0 A- |9 J" A( gupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句

5 e; E; i* R  G, O+ x$ b0 A# w
! U% Z" N: Y; w* L5 g8 J! A3 m% @9 ICODE:

) V4 ?- N4 a  S4 |1 W! y
drop table tmp;
6 v! S* @( e3 z$ f6 v/ G1 Ecreate table tmp
$ h; j$ @$ I/ h" p9 t(
8 @( ?2 N! q& }. T9 T[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
; K- D2 `1 ~" i6 R% g1 c" z: G[depth] [int] NOT NULL
);
7 {, }; H9 }) G) W
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
0 {' ], Q5 J9 k! yinsert into tmp exec master..xp_dirtree @root,0,0
' h! {; w* ^" h) R) Iset @id=(select top 1 id from tmp where name=@name)
4 I, q4 Y# _$ h, w) tset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
' M- T, w# {# n4 p, y( |/ ~& |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)
1 u0 {- Y8 K3 d3 I, Y( V3 |- dset @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