34,590
社区成员
发帖
与我相关
我的任务
分享
/* 在SQL Server中取得操作系统文件的最后修改日期
获取文件最后访问日期
@filepath 文件路径,如: c:\1.txt
@filedate 文件最后访问日期
调用示例:
declare @dt varchar(20)
exec getFileLastAccessDate 'c:\1.txt',@dt output
select @dt
*/
create procedure getFileLastAccessDate
@filepath varchar(4000),
@filedate varchar(20) output
as
declare @obj int,@file int
declare @fileexists varchar(10)
exec sp_oacreate 'Scripting.FileSystemObject',@obj output
exec sp_oamethod @obj,'FileExists',@fileexists output,@filepath
if @fileexists='False'
begin
set @filedate='文件不存在'
return
end
exec sp_oamethod @obj,'GetFile',@file output,@filepath
exec sp_oagetproperty @file,'DateLastAccessed',@filedate output
go
/*
获取文件最后修改日期
@filepath 文件路径,如: c:\1.txt
@filedate 文件最后修改日期
调用示例:
declare @dt varchar(20)
exec getFileLastModified 'c:\1.txt',@dt output
select @dt
*/
create procedure getFileLastModified
@filepath varchar(4000),
@filedate varchar(20) output
as
declare @obj int,@file int
declare @fileexists varchar(10)
exec sp_oacreate 'Scripting.FileSystemObject',@obj output
exec sp_oamethod @obj,'FileExists',@fileexists output,@filepath
if @fileexists='False'
begin
set @filedate='文件不存在'
return
end
exec sp_oamethod @obj,'GetFile',@file output,@filepath
exec sp_oagetproperty @file,'DateLastModified',@filedate output
go
if object_id('tempdb.dbo.#') is not null
drop table #
go
create table #(s varchar(8000))
insert into # exec master..xp_cmdshell 'dir D:\Data'
declare @sql varchar(8000)
select @sql=isnull(@sql+';','')+'exec master..xp_cmdshell ''del D:\Data\'+right(s,charindex(' ',reverse(s))-1)+''''
from # where s like '%.bak'
exec( @sql )
if object_id('tempdb.dbo.#') is not null
drop table #
go
create table #(s varchar(8000))
insert into # exec master..xp_cmdshell 'dir D:\Data'
select * from # where s like '%.bak'