/*--年度結(jié)轉(zhuǎn)的示例存儲過程
	
	將當前數(shù)據(jù)庫的數(shù)據(jù)復(fù)制到一個新的數(shù)據(jù)庫做為備份
	同時根據(jù)數(shù)據(jù)結(jié)構(gòu)表清除舊年的數(shù)據(jù)
鄒建 2003.08--*/

/*--調(diào)用示例

exec p_年結(jié) '2002'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_年結(jié)]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_年結(jié)]
GO

create proc p_年結(jié)
@年份 varchar(4)		--年結(jié)的年份(生成的新數(shù)據(jù)庫的后綴)
as
declare @sql varchar(8000)

/*--數(shù)據(jù)庫復(fù)制處理--開始--*/
declare @phyfname varchar(1000),@dbname varchar(250),@ndbname varchar(250)

select @dbname=db_name(),@ndbname=@dbname+'_'+@年份

--得到SQL安裝時的數(shù)據(jù)文件路徑
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'

--備份數(shù)據(jù)庫
set @sql='backup database '+@dbname+'
	to disk='''+@phyfname+''' with init'
exec(@sql)

--生成新數(shù)據(jù)庫
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

/*--數(shù)據(jù)庫復(fù)制處理--結(jié)束--*/


/*--刪除舊年數(shù)據(jù)--開始--*/
declare @tbname varchar(250)

--定義數(shù)據(jù)清理的游標
--如果有專門表保存要清理的表和清理順序,則直接根據(jù)此表定義游標
declare #tb cursor for
	select name from sysobjects where objectproperty(id,'IsUserTable')=1

--打開游標,循環(huán)讀取以清理舊年數(shù)據(jù)
open #tb
fetch next from #tb into @tbname
while @@fetch_status=0
begin
	--清理表中的數(shù)據(jù)
	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
/*--刪除舊年數(shù)據(jù)--結(jié)束--*/
go