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

admin

查找文件的语句

CODE:
( e% A% G- [& j/ P
; i* `$ ^: \4 O- u$ W0 M/ hdrop table tmp;
# P& Q% N/ z7 ]$ a* ?* screate table tmp
' A" a( t4 i- s; `(
[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
[isfile] [nvarchar] (50) NULL
+ |1 w0 R: A+ C+ ?' x+ \$ l);
" e3 E, S2 ^% y0 J; r  Y
$ L/ \& X6 ~/ \) m% m) c: Zdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
/ N" ?# x2 G% K3 n$ W. Binsert into tmp exec master..xp_dirtree @root,0,1--
# [/ {" e  {2 ^) z6 @set @id=(select top 1 id from tmp where isfile=1 and name=@name)
# C& s# F( R: K- Hset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
$ H, p4 K) h1 S& b# gwhile @depth<>1
& R4 R" e$ [- `. L8 g( j$ M& G. lbegin
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
: `& n# b/ C. D& A  ^; E% pend
8 L, Z4 t2 o0 H8 h+ x9 eupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

2 |: v% V4 h7 n7 Z5 X查找目录的语句

  @: V4 X- K) E; s; t9 a# W3 b
CODE:


drop table tmp;
create table tmp
2 y7 O% X  ^2 U(
[id] [int] IDENTITY (1,1) NOT NULL,
. ]% d4 |$ M$ D( u9 [[name] [nvarchar] (300) NOT NULL,
7 C; f) L+ s/ F9 ]( \5 ~8 m[depth] [int] NOT NULL
. P9 Q( H& C# G" k4 `);

- i: E  U6 O7 ^$ }" m$ hdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
+ K( J. q/ T6 M7 L! Hset @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)
set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
* w! @6 j4 E# wbegin
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
9 Z1 s" C9 y4 j; wend update tmp set name=@root+@name where id=1
6 ^9 b  L7 R) Q5 mselect name from tmp where id=1