Generating Archive Tables and Triggers

create proc CreateArchive(@tablename nvarchar(200), @schema nvarchar(200) = 'dbo')
as
begin

declare @sql nvarchar(4000)
set @sql = '
create table archives.'+@tablename+'Arc  
([archiveId] [int] IDENTITY(1,1) NOT NULL,
[archiveAction] [char](1) NOT NULL,
[archiveBy] [varchar](128) NULL,
[archived] [datetime] NULL, 
'


select @sql = @sql + '['+c.COLUMN_NAME+'] ' +
+ c.DATA_TYPE +
CASE WHEN c.CHARACTER_MAXIMUM_LENGTH > 0
THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN c.CHARACTER_MAXIMUM_LENGTH = -1
THEN '(MAX)'
ELSE '' END +
' NULL, 
'
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @tablename
and TABLE_SCHEMA = @schema
order by c.ORDINAL_POSITION


set @sql = @sql + 'CONSTRAINT [PK_'+ @tablename + 'Arc] PRIMARY KEY CLUSTERED 
(
    [archiveId] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

ALTER TABLE [archives].[' + @tablename + 'Arc] 
    ADD CONSTRAINT [DF_' + @tablename + 'Arc_archiveBy]  
    DEFAULT (user_name()) FOR [archiveBy];
ALTER TABLE [archives].[' + @tablename + 'Arc] 
    ADD CONSTRAINT [DF_' + @tablename + 'Arc_archived]
    DEFAULT (getdate()) FOR [archived];

grant all on [archives].[' + @tablename + 'Arc] to public;'

print @sql
exec sp_executesql @sql

set @sql = '
Create trigger [' + @schema + '].[t' + @tablename + 'Arc] 
    on [' + @schema + '].[' + @tablename + ']
for update, delete
as
begin

set nocount on
declare @Action char(1)

if exists (select 1 from inserted)
    set @Action = ''U''
else
    set @Action = ''D''


insert into archives.[' + @tablename + 'Arc]
(archiveAction'

select @sql = @sql + ', ['+c.COLUMN_NAME+']'
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @tablename
order by c.ORDINAL_POSITION

set @sql = @sql + ') 
select @Action'
select @sql = @sql + ', ['+c.COLUMN_NAME+']'
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @tablename
order by c.ORDINAL_POSITION

set @sql = @sql + '
from deleted

end;'


print @sql
exec sp_executesql @sql

end
go

-- create the archives schema
create schema archives

-- Generate the commands that call the stored proc for each table:
select 'exec CreateArchive '+TABLE_NAME + ';'
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'dbo'
order by TABLE_NAME

-- drop the tables if I got an error:
select 'drop table archives.'+TABLE_NAME + ';'
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'archives'
order by TABLE_NAME


-- drop the triggers if I got an error:
select 'drop trigger '+name + ';'
from sys.triggers
where create_date > getdate() - 1
order by name

-- see what object were last changed in the database
select *
from sys.objects
order by modify_date desc