The following is a script that generates an audit table for a provided table name.

It will:
– Add 5 audit columns to an existing table (DataVersion, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy)
– Create 2 triggers on the production table, one for updates and one for deletes (inserts do not need audit info)
– Create an audit table with similar design as the production table
– Create a primary key index on the audit table matching that of the production table but with the DataVersion also added as a part of it

Prerequisites:
– The stored procedure “sp_ScriptTableForAudit” must be present in the database (provided here)
– The script must be run in the database that the production table resides.
– The script does not handle table names with other schemas than dbo
– The table must have a primary key

Please add feedback/bug reports etc as comments and I will try to fix them right away

/*
Script created by Henning Frettem, aka Lumbago @ SQLTeam, 2010-03-23
BE CAREFUL, THIS SCRIPT WILL MAKE CHANGES TO YOUR TABLES!!
*/

SET NOCOUNT ON

BEGIN TRY

	DECLARE @TableName varchar(100); SET @TableName = 'myTable'
	DECLARE @AuditTableName varchar(200)

	--> Checking prerequisites
	IF NOT EXISTS (
			SELECT 1
			FROM sys.index_columns sc
			  JOIN sys.indexes i
				ON sc.object_id = i.object_id
				  AND sc.index_id = i.index_id
			WHERE OBJECT_NAME(sc.object_id) = @TableName 
			  AND i.is_primary_key = 1)
		RAISERROR('There is no primary key on the table', 16, 1)

	IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_ScriptTableForAudit')
		RAISERROR('The stored procedure sp_ScriptTableForAudit is not present in the database', 16, 1)

	SET @AuditTableName = @TableName + '_audit'

	DECLARE @SQL nvarchar(max); SET @SQL = ''

	--> Add audit columns to the production table if they don't exist already
	IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'DataVersion')
	  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD DataVersion int NOT NULL DEFAULT 1' + CHAR(13)

	IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedDate')
	  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)

	IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedBy')
	  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)

	IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedDate')
	  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)

	IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedBy')
	  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)

	IF LEN(@SQL) > 0
	  BEGIN
		PRINT @SQL
		EXEC sp_executesql @SQL
		SET @SQL = ''
	  END

	--> Create the audit table with the same definition as the production table, but add the IsDeleted column
	IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @AuditTableName)
	  BEGIN
		DECLARE @AuditTableDefinition table (
		  RowNumber int IDENTITY(1, 1),
		  DefinitionRow varchar(max)
		  )

		INSERT INTO @AuditTableDefinition
		EXEC sp_ScriptTableForAudit @Tablename = @TableName, @NewTablename = @AuditTableName

		--> Need IsDeleted column in audit table
		UPDATE @AuditTableDefinition
		  SET DefinitionRow = ', IsDeleted bit NOT NULL DEFAULT(0) )'
		WHERE RowNumber = (SELECT MAX(RowNumber) FROM @AuditTableDefinition)

		SELECT @SQL = @SQL + DefinitionRow FROM @AuditTableDefinition

		--> Run the create table script in the database
		PRINT @SQL
		EXEC sp_executesql @SQL
		SET @SQL = ''
	  END

	--> Create Update trigger
	IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Update')
	  BEGIN
		SET @SQL = @SQL + '
		  CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Update] ON ' + @TableName + '
		  FOR UPDATE
		  AS

		  SET NOCOUNT ON

		  UPDATE a SET
			a.DataVersion = b.DataVersion + 1,
			a.ModifiedDate = GETDATE(),
			a.ModifiedBy = SYSTEM_USER
		  FROM ' + @TableName + ' a
			INNER JOIN inserted b
			  ON '

		SELECT @SQL = @SQL + 'a.' + c.name + ' = b.' + c.name + ' AND '
		FROM sys.index_columns sc
		  JOIN sys.columns c
			ON sc.object_id = c.object_id
			  AND sc.column_id = c.column_id
		  JOIN sys.indexes i
			ON sc.object_id = i.object_id
			  AND sc.index_id = i.index_id
		WHERE OBJECT_NAME(sc.object_id) = @TableName
		  AND i.is_primary_key = 1
		ORDER BY index_column_id ASC

		SET @SQL = LEFT(@SQL, LEN(@SQL) - 4) + CHAR(13) + CHAR(13)

		SET @SQL = @SQL + '  INSERT INTO ' + @AuditTableName + '
			('

		SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @AuditTableName

		SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
		SET @SQL = @SQL + ')
		  SELECT '

		SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @AuditTableName

		SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
		SET @SQL = @SQL + ' = 0
		  FROM deleted

		  GO
		  '

		PRINT @SQL
		EXEC sp_executesql @SQL
		SET @SQL = ''
	  END

	--> Create delete trigger
	IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Delete')
	  BEGIN
		SET @SQL = @SQL + '
		  CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Delete] ON ' + @TableName + '
		  FOR DELETE
		  AS

		  SET NOCOUNT ON

		  INSERT INTO ' + @AuditTableName + '
			('

		SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @AuditTableName

		SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
		SET @SQL = @SQL + ')
		  SELECT '

		SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @AuditTableName

		SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
		SET @SQL = @SQL + ' = 1
		  FROM deleted

		  GO'

		PRINT @SQL
		EXEC sp_executesql @SQL
		SET @SQL = ''
	  END
END TRY
BEGIN CATCH
    PRINT Error_Message()
END CATCH