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

admin

查找文件的语句

+ F. B2 l4 w$ E1 p4 n3 _CODE:

* `5 U2 D' D) U9 }% S3 f5 t; x( t/ Idrop table tmp;
8 R9 n' i: O2 A& v4 Wcreate table tmp
/ Y) a& L3 b3 }6 [(
4 x$ ?; J; t# b[id] [int] IDENTITY (1,1) NOT NULL,
[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL,
& q2 W3 I4 {* w1 P5 G& ^/ S[isfile] [nvarchar] (50) NULL
' f4 f8 \. O$ R  V& g( c/ O);

: I3 K7 ?8 v  T1 L# Qdeclare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='cmd.exe'   -- Find file
$ \" i) ?) _/ U$ p9 L% ainsert into tmp exec master..xp_dirtree @root,0,1--
set @id=(select top 1 id from tmp where isfile=1 and name=@name)
6 `- M  X; x+ m  @9 wset @depth=(select top 1 depth from tmp where isfile=1 and name=@name)
while @depth<>1
begin
* P. n9 F& @( R* r0 ]0 O/ K# Uset @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)
7 N5 M5 }! M, S$ E1 d( uset @name=(select name from tmp where id=@id)+'\'+@name
end
2 T3 {  k1 X/ nupdate tmp set name=@root+@name where id=1
select name from tmp where id=1

查找目录的语句
7 K( }: g- `( k9 E" r

% q4 g+ f# O, n# T2 o  j0 {CODE:
. i% W  |# G. h' _1 z
. |8 W( A$ V* Q0 G
$ M( z- E- N; ~$ Idrop table tmp;
create table tmp
3 N' p3 ^1 x2 a' h(
  v: v# o6 c/ g9 f[id] [int] IDENTITY (1,1) NOT NULL,
) o  `9 Z# H1 r  _, O, ?[name] [nvarchar] (300) NOT NULL,
[depth] [int] NOT NULL
5 n# [7 J: n/ y2 X);
- s, u2 h; C, g2 ^8 y
0 a% I+ a* F7 G: |declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300)
set @root='f:\usr\' -- Start root
set @name='donggeer' -- directory to find
! t* a8 s' o* @7 ?6 n/ n7 @6 pinsert into tmp exec master..xp_dirtree @root,0,0
$ B0 c- @5 a+ z' Pset @id=(select top 1 id from tmp where name=@name)
set @depth=(select top 1 depth from tmp where name=@name)
while @depth<>1
begin
3 m/ R$ i5 c, u. l7 E# c5 Hset @id=(select top 1 id from tmp where id<@id and depth=(@depth-1) order by id desc)
+ w, G% r2 Y0 M) |$ dset @depth=(select depth from tmp where id=@id)
set @name=(select name from tmp where id=@id)+'\'+@name
: l7 C; N2 U/ s& ^( Z$ X0 T: U, rend update tmp set name=@root+@name where id=1
select name from tmp where id=1