195.年度结转的存储过程

た入场券 2024-01-20 07:06 30阅读 0赞

/*--年度结转的示例存储过程
    	
    	将当前数据库的数据复制到一个新的数据库做为备份
    	同时根据数据结构表清除旧年的数据
    --*/
    
    /*--调用示例
    
    exec p_年结 '2002'
    --*/
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_年结]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[p_年结]
    GO
    
    create proc p_年结
    @年份 varchar(4)		--年结的年份(生成的新数据库的后缀)
    as
    declare @sql varchar(8000)
    
    /*--数据库复制处理--开始--*/
    declare @phyfname varchar(1000),@dbname varchar(250),@ndbname varchar(250)
    
    select @dbname=db_name(),@ndbname=@dbname+'_'+@年份
    
    --得到SQL安装时的数据文件路径
    select @phyfname=rtrim(reverse(filename)) from master..sysfiles where name='master'
    select @phyfname=reverse(substring(@phyfname,charindex('\',@phyfname),8000))
    	,@phyfname=@phyfname+@dbname+'_'+convert(varchar(10),getdate(),112)+'_temp.bak'
    
    --备份数据库
    set @sql='backup database '+@dbname+'
    	to disk='''+@phyfname+''' with init'
    exec(@sql)
    
    --生成新数据库
    set @sql='restore database '+@ndbname+'
    	from disk='''+@phyfname+'''
    	with '
    select @sql=@sql+'move '''+rtrim(lname)
    	+''' to '''+rtrim(pname)+''','
    from (select lname=cast(name as varchar)
    		,pname=cast(stuff(filename
    			,len(filename)-charindex('\',ltrim(reverse(filename)))+2
    			,len(@dbname),@ndbname) as varchar)
    		from sysfiles
    ) a
    
    set @sql=left(@sql,len(@sql)-1)
    exec(@sql)
    
    --删除临时备份文件
    set @sql='del "'+@phyfname+'"'
    exec master..xp_cmdshell @sql
    
    /*--数据库复制处理--结束--*/
    
    
    /*--删除旧年数据--开始--*/
    declare @tbname varchar(250)
    
    --定义数据清理的游标
    --如果有专门表保存要清理的表和清理顺序,则直接根据此表定义游标
    declare #tb cursor for
    	select name from sysobjects where objectproperty(id,'IsUserTable')=1
    
    --打开游标,循环读取以清理旧年数据
    open #tb
    fetch next from #tb into @tbname
    while @@fetch_status=0
    begin
    	--清理表中的数据
    	set @sql='delete from ['+@tbname+']'
    	exec(@sql)
    
    	--如果表含有标识列,将它重新初始化
    	if exists(select 1 from syscolumns where object_id(@tbname)=id and status=0x80)
    		DBCC CHECKIDENT (@tbname, RESEED, 1)
    	fetch next from #tb into @tbname
    end
    close #tb
    deallocate #tb
    /*--删除旧年数据--结束--*/
    go