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

admin

查找文件的语句
0 L4 B5 z# ^9 `2 T- v
CODE:

drop table tmp;
* |  p. `2 ~. o! Wcreate table tmp
(
[id] [int] IDENTITY (1,1) NOT NULL,
- |6 c0 U5 j; u[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
);
4 Y+ N) g! r6 ?# X
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
" s# i: p5 y5 _1 j3 D& U% pset @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
6 \. q" ~2 X/ J2 zinsert 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)
+ l( [# ~# A, ~9 B1 L# dwhile @depth<>1
! X5 R  r$ n" b# b6 f  Wbegin
4 Z- \7 q! B' h" p+ Jset @id=(select top 1 id from tmp where isfile=0 and id<@id and depth=(@depth-1) order by id desc)
1 q. r' A+ c& _. r# u- g+ \; m. F( Sset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
$ T& h1 l5 j0 ?; O+ K2 Dend
8 h0 U+ M# {% M/ h+ |update tmp set name=@root+@name where id=1
select name from tmp where id=1
' [, d. Y/ `: e2 w( I, s- U' F
查找目录的语句

/ C% n# V/ w8 }1 O0 [: U3 X
CODE:
0 o. @# E$ V6 q% H; m

drop table tmp;
create table tmp
9 {8 k; `$ [  }/ S+ f7 O2 V0 p; J2 C(
. F! i( ~; o! X+ L' M[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
8 a* V! u# l5 b0 A& c! h" ~4 r);
) P1 B6 J6 e3 {2 l' {
9 E$ N* Q6 C6 Bdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
. k' |1 O# F3 B, V# f% y! W) Hset @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
2 G3 A5 y. U8 Winsert into tmp exec master..xp_dirtree @root,0,0
0 W( ~) g  t0 o6 W0 W: `set @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
* |7 ^( a1 `+ P! Jwhile @depth<>1
begin
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
! @8 _! w1 v% {1 j0 M: s! t4 cend update tmp set name=@root+@name where id=1
) ]+ s. d, D$ ^select name from tmp where id=1