查找文件的语句
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 );
declare @id int, @depth int, @root nvarchar(300), @name nvarchar(300) set @root=‘f:\usr’ – Start root set @name=‘cmd.exe’ – Find file insert 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 end update tmp set name=@root+@name where id=1 select name from tmp where id=1
查找目录的语句
CODE:
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 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 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 end update tmp set name=@root+@name where id=1 select name from tmp where id=1
