SQL Audit Part 5: Altering the Tables - Source Defined Fields

So, we have now got a manageable setup that will allow us to add the audit required fields to the audit and archive tables. Now we need to replicate the source table structure in those tables. The basics are the same with the audit fields, but we get the structure from the source table.

CREATE TABLE #adtSourceTable (
FieldName varchar(255),
FieldType varchar(255),
FieldSize int
);
SET @sql = N'Insert into #adtSourceTable
Select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from '+@databaseName+'.information_schema.columns
where TABLE_NAME like '''+@TableName+'''';
EXEC sp_executesql @sql;

If you look above you will see how we get the list of fields and their definition.  We look at a table called information_schema.columns to get the information that we need.  Now this table will get more fields than we actually need, so we only get the fields that we do need, namely the name, type and size or length.

The rest of this part of the code is almost an exact mirror of the code that creates the audit fields in both of the tables.