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

admin

查找文件的语句
' g7 w! I! ~1 n
CODE:
# t: s/ Q" N. e0 u
drop table tmp;
! `6 R! T# `- wcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
' G" r; v$ n9 N4 m7 B5 T, `4 L
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
; W( g  C5 z# Y; o+ Z+ F. _/ zset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
7 T# T0 r- e0 q0 i: w3 R0 r# Pinsert 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
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
  \1 ~& J' }- O, y4 S: zend
' S7 Z; x. }  D) U2 r( Y3 ]6 gupdate tmp set name=@root+@name where id=1
select name from tmp where id=1
) |/ N& v+ `' ^" N* Y
! V, O4 j! L3 t& W查找目录的语句


% m" _( V6 p" SCODE:

+ U$ h  p0 @& w  T& z# j; t7 g
drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
2 Q. F) Y1 E% s  i" winsert into tmp exec master..xp_dirtree @root,0,0
( s) ?4 U, i) u8 z. U, s6 Xset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
# Y5 R* q6 J5 E( @* _1 d" L) M/ h) Wbegin
; u9 e+ o  M; kset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
9 z% c9 q5 ?3 k, Zset @depth=(select depth from tmp where id=@id)
4 r1 S  R% S' B; p+ _8 }set @name=(select name from tmp where id=@id)+'\'+@name
end update tmp set name=@root+@name where id=1
$ ?( C7 s: l# i/ ~& @' u" _select name from tmp where id=1