One way would be to use the following code. This should dynamically duplicate a table, preserving the data types, and create an INSERT,UPDATE,DELETE trigger.

This trigger will insert 2 records on an UPDATE, an ‘Update – inserted’ record, and an ‘Update – deleted’ record. (This was for a business rule.)

You would need to change ‘AUDIT_1’ to change your table prefix to whatever you want. Thanks to all for the help on this question.

DECLARE @TableName SYSNAME
DECLARE @SchemaName SYSNAME

DECLARE @SQL NVARCHAR(MAX) = 'CREATE TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TABLE_PREFIX' + SUBSTRING(@TableName, 3, LEN(@TableName)-2)) + ' (
    [AuditId] INT IDENTITY(1,1) NOT NULL,
    [AuditAction] VARCHAR(50) NOT NULL,
    [AuditDateTime] DATETIME NOT NULL,    
    ' + 
    STUFF((
        SELECT  ',' + '[' + c.name + '] ' + 
                CASE 
                    WHEN c.system_type_id IN (167, 175, 231, 239) AND c.max_length = -1 THEN 'VARCHAR(MAX)'
                    WHEN c.system_type_id IN (167, 175, 231, 239) THEN 'VARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (165, 173, 231, 239) AND c.max_length = -1 THEN 'NVARCHAR(MAX)'
                    WHEN c.system_type_id IN (165, 173, 231, 239) THEN 'NVARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length / 2 END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (40) THEN 'CHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (41) THEN 'NCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (48, 52, 56) THEN 'INT'
                    WHEN c.system_type_id IN (127) THEN 'BIGINT'
                    WHEN c.system_type_id IN (59, 60, 62) THEN 'SMALLINT'
                    WHEN c.system_type_id IN (106) THEN CONCAT('DECIMAL(', [precision], ',', scale, ')')
                    WHEN c.system_type_id IN (108) THEN CONCAT('NUMERIC(', [precision], ',', scale, ')')
                    WHEN c.system_type_id = 104 THEN 'BIT' -- Changed from 'TINYINT' to 'BIT'
                    ELSE TYPE_NAME(c.system_type_id)
                END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
        FROM    sys.columns c
        WHERE   c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
        ORDER BY c.column_id
        FOR XML PATH(''), TYPE
    ).value('.[1]','nvarchar(max)'), 1, 1, '')
    + ')'
EXEC(@SQL)


    /* Create Trigger */

SET @SQL = 'CREATE TRIGGER [tg_' + @TableName + '_AUD1' +'] ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) +'
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

            IF EXISTS (SELECT   * FROM  INSERTED)
            BEGIN
                IF EXISTS (SELECT   * FROM  DELETED)
                    BEGIN  --UPDATE
                                        
                    INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TABLE_PREFIX' + SUBSTRING(@TableName, 3, LEN(@TableName)-2))+ ' (
                        AuditAction,
                        AuditDateTime,                  
                        ' + STUFF((
                            SELECT ',' + QUOTENAME(c.name)
                            FROM sys.columns c
                            WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
                            FOR XML PATH(''), TYPE
                        ).value('.[1]','nvarchar(max)'), 1, 1, '') + '
                    )
                    SELECT
                        ''UPDATE - Inserted'',
                        SYSDATETIME(),                      
                        ' + STUFF((
                            SELECT ',' + CASE WHEN c.name IN ('RowGuid', 'ModifiedDate') THEN 'DEFAULT' ELSE 'i.' + QUOTENAME(c.name) END
                            FROM sys.columns c
                            WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
                            FOR XML PATH(''), TYPE
                        ).value('.[1]','nvarchar(max)'), 1, 1, '') + '
                    FROM inserted i;
            
                    INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TABLE_PREFIX' + SUBSTRING(@TableName, 3, LEN(@TableName)-2))+ ' (
                        AuditAction,
                        AuditDateTime,                  
                        ' + STUFF((
                            SELECT ',' + QUOTENAME(c.name)
                            FROM sys.columns c
                            WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
                            FOR XML PATH(''), TYPE
                        ).value('.[1]','nvarchar(max)'), 1, 1, '') + '
                    )
                    SELECT
                        ''UPDATE - Deleted'',
                        SYSDATETIME(),                      
                        ' + STUFF((
                            SELECT ',' + CASE WHEN c.name IN ('RowGuid', 'ModifiedDate') THEN 'DEFAULT' ELSE 'd.' + QUOTENAME(c.name) END
                            FROM sys.columns c
                            WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
                            FOR XML PATH(''), TYPE
                        ).value('.[1]','nvarchar(max)'), 1, 1, '') + '
                    FROM deleted d;
                    END;
                ELSE
                    BEGIN       --INSERT
                                            INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TABLE_PREFIX' +  SUBSTRING(@TableName, 3, LEN(@TableName)-2)) + ' (
                        AuditAction,
                        AuditDateTime,                      
                        ' + STUFF((
                            SELECT ',' + QUOTENAME(c.name)
                            FROM sys.columns c
                            WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
                            FOR XML PATH(''), TYPE
                        ).value('.[1]','nvarchar(max)'), 1, 1, '') + '
                    )
                    SELECT
                        ''INSERT'',
                        SYSDATETIME(),                      
                        ' + STUFF((
                            SELECT ',' + CASE WHEN c.name IN ('RowGuid', 'ModifiedDate') THEN 'DEFAULT' ELSE 'i.' + QUOTENAME(c.name) END
                            FROM sys.columns c
                            WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
                            FOR XML PATH(''), TYPE
                        ).value('.[1]','nvarchar(max)'), 1, 1, '') + '
                    FROM inserted i;
                    END;
            END;
        ELSE
            BEGIN  --DELETE
                                INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TABLE_PREFIX' + SUBSTRING(@TableName, 3, LEN(@TableName)-2))+ ' (
                    AuditAction,
                    AuditDateTime,                  
                    ' + STUFF((
                        SELECT ',' + QUOTENAME(c.name)
                        FROM sys.columns c
                        WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
                        FOR XML PATH(''), TYPE
                    ).value('.[1]','nvarchar(max)'), 1, 1, '') + '
                )
                SELECT
                    ''DELETE'',
                    SYSDATETIME(),                  
                    ' + STUFF((
                        SELECT ',' + CASE WHEN c.name IN ('RowGuid', 'ModifiedDate') THEN 'DEFAULT' ELSE 'd.' + QUOTENAME(c.name) END
                        FROM sys.columns c
                        WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
                        FOR XML PATH(''), TYPE
                    ).value('.[1]','nvarchar(max)'), 1, 1, '') + '
                FROM deleted d;
            END;
END'
EXEC(@SQL)

Also, if you need to quickly drop the triggers an an audit table, you can use the following code to get you there quickly. This code looks for ‘AUDIT_1’ in the name.

/* CODE TO QUICKLY DROP ALL TRIGGERS IN SCHEMA */
                                                                                
    DROP TABLE IF EXISTS #t1;
    SELECT      sch.name                                                                                      AS [Schema]
              , t.name                                                                                        AS [Table]
              , tr.name                                                                                       AS [Trigger]
              , tr.is_disabled
              , CASE tr.type WHEN 'TR' THEN 'SQL Trigger'
                             WHEN 'TA' THEN 'CLR Trigger' END                                                 AS [Trigger Type]
              , tr.create_date
              , tr.modify_date
              , CASE WHEN tr.is_instead_of_trigger = 1 THEN 'INSTEAD OF' ELSE 'AFTER' END                     AS [Trigger Timing]
              , CASE WHEN OBJECTPROPERTY(tr.object_id, 'ExecIsInsertTrigger') = 1 THEN 'INSERT,' ELSE '' END
                + CASE WHEN OBJECTPROPERTY(tr.object_id, 'ExecIsUpdateTrigger') = 1 THEN 'UPDATE,' ELSE '' END
                + CASE WHEN OBJECTPROPERTY(tr.object_id, 'ExecIsDeleteTrigger') = 1 THEN 'DELETE' ELSE '' END AS [Trigger Events]
      INTO      #t1
      FROM      sys.triggers AS tr
     INNER JOIN sys.tables   AS t ON tr.parent_id  = t.object_id
     INNER JOIN sys.schemas  AS sch ON t.schema_id = sch.schema_id
     WHERE      sch.name LIKE 'field%' AND  tr.name LIKE '%AUDIT_1';
    
    SELECT  'DROP TRIGGER IF EXISTS ' + t1.[Trigger]
          , *
      FROM  #t1 AS t1;

/* CODE TO QUICKLY DROP ALL AUDIT TABLES IN SCHEMA */                                                                           
    
    SELECT  SCHEMA_NAME(t.schema_id) AS schema_name
          , t.name                   AS table_name
      INTO  #t1
      FROM  sys.tables AS t
     WHERE  t.name LIKE 't_Audit%' AND  SCHEMA_NAME(t.schema_id) LIKE 'Field%'
     ORDER BY table_name
            , schema_name;
    
    SELECT  *
          , 'Drop table ' + t1.schema_name + '.' + t1.table_name
      FROM  #t1 AS t1
     WHERE  t1.table_name NOT LIKE '%fields%' OR t1.table_name NOT LIKE '%rows%';