SQL Server的文件监控

SQL Server检查上传的文件：

/*

上传文件的路径格式为：

\\192.168.10.3\A\服务器名称\数据库名称\文件名

*/

if object_id(N'#dbname') is not null 
Begin
    drop table #dbname
End
if object_id(N'#result') is not null 
Begin
    drop table #result
End
create table #dbname(ID int IDENTITY(1,1)not null,dbname varchar(50),time varchar(20),result bit default 0)
create table #result(ID int IDENTITY(1,1)not null,a bit,b bit,c bit)


declare @filepath varchar(200),@path varchar(50),@servername varchar(50),@dbname varchar(50),@filename varchar(50),
@time varchar(20),@fulltime varchar(10),@num int,@sum int
set @path='\\192.168.10.3\A\'  --上传文件的路径
select @servername=@@SERVERNAME
set @num=1
insert into #dbname (dbname,time)select distinct bp.[database_name],CONVERT(varchar(20),max(bp.[backup_start_date]),112) 
from [msdb].[dbo].[backupset] bp
where type='D' and bp.database_name in (select DB_NAME(database_id) from sys.master_files)
group by bp.[database_name]
select @sum=COUNT(*) from #dbname


while(@num<=@sum)
begin
select @dbname=dbname,@time=time from #dbname where id=@num
set @filepath=@path+@servername+'\'+@dbname+'\'+'文件名'
insert into #result exec master..xp_fileexist @filepath
update #dbname set result=(select a from #result where id=@num)where id=@num
set @num=@num+1
end
select * from #dbname
drop table #result
drop table #dbname