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

admin

查找文件的语句

CODE:

drop table tmp;
create table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
4 J) q9 @; R8 W1 b. K);

declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
' Y- z4 N( @0 l# Linsert into tmp exec master..xp_dirtree @root,0,1--
) s7 r. r4 _9 G7 m5 k, y" ?! W, Fset @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)
. ?2 s* {8 e# c4 ^5 i& Gwhile @depth<>1
- i; l/ W( v# ^  Ubegin
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
. `; p/ M% d' G% y1 C! [end
update tmp set name=@root+@name where id=1
& N; V. U" k6 P. s6 D4 C, \5 Uselect name from tmp where id=1
- {3 h0 L# s3 v% Y3 ^7 T5 O7 \
% |; ?! v; K+ L8 R查找目录的语句

) `' L# L# _* L8 D; I% g4 R
0 H* y3 l( \; u# n* u0 X0 `CODE:

- a( F' s* [9 _4 U
drop table tmp;
9 Y; Z. A; U& |1 \3 N! A9 ycreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
);
8 V. \$ k6 w7 _  g4 p
; F5 Q4 s% d* E. Tdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
# o* e! i7 v) y3 R! ^3 X* yset @root='f:\usr\' -- Start root
" s$ u& e1 H( D( ]  q* z( l: u( Eset @name='donggeer' -- directory to find
insert into tmp exec master..xp_dirtree @root,0,0
& P3 ]5 t  Y3 w6 g& Vset @id=(select top 1 id from tmp where name=@name)
# O, n/ ~6 g( q0 O! rset @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
5 r) \+ b$ Y# }1 p! Sset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
2 b: h! B' {# A) ~set @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
) }: o, q( E0 Y( Gend update tmp set name=@root+@name where id=1
; P# n: K% q3 ]  l8 l- u! pselect name from tmp where id=1