/*purpose: archive passed target table with date time stamp. original table is copied as a shell
this is referred to as a table swap*/
ALTER procedure [dbo].[sp_archive_target_table]
@target varchar(100)
--set @target='table_name'
AS
--=====================
declare @CREATE varchar(4000)
declare @RENAME_ORIG varchar(4000)
declare @RENAME_NEW varchar(4000)
declare @append varchar(16)
--========================
set @append=CONVERT(varchar(8),getdate(),112)+REPLACE(CONVERT(VARCHAR(8),GETDATE(),114),':','')
--========================
set @CREATE='
select top 0 *
into '+@target+'_new
from '+@target+''
exec (@CREATE)
--========================
set @RENAME_ORIG='
sp_rename '''+@target+''', '''+@target+''+'_'+''+@append+'''
'
exec (@RENAME_ORIG)
set @RENAME_NEW='
sp_rename '''+@target+'_new'', '''+@target+'''
'
exec (@RENAME_NEW)
--=======================
