USE master
GO
CREATE PROCEDURE sp_CreateDataAudit
	@table_schema sysname = NULL, --Schema of the table to audit (optional but recommended)
	@table_name sysname, --Name of the table to audit
	@audit_table_schema sysname = NULL, --Schema in which to create the audit table (optional but recommended)
	@audit_table_name sysname, --Name of the audit table to create
	@audit_table_filegroup sysname = NULL, --Filegroup in which to create the audit table (optional)
	@audit_trigger_name sysname, --Name of the trigger to create on the table being audited
	@include_cols nvarchar(max), --Columns to include and watch for changes. Primary key columns are always included. (optional, all columns will be included if ommitted)
	@force_varchar_varbinary bit = 0 --Create char/nchar/binary columns as varchar/nvarchar/varbinary in the audit table
AS
--NOTE: This procedure will not configure any INSERT permissions on the audit table.
--The schema for the audit table must already exist.
--The audit table will be created with a clustered index on the primary key + _Changed.

DECLARE @crlf nchar(2) = CHAR(13) + CHAR(10) --Carriage return/line feed constant
DECLARE @sql nvarchar(max) --Dynamic SQL
DECLARE @msg nvarchar(max) --Generic output message variable
DECLARE @c nvarchar(max) --Column list for CREATE TABLE statement
DECLARE @c_insert nvarchar(max) --Column list for INSERT statement in trigger
DECLARE @key_cols_join nvarchar(max) --List of key columns for JOIN clause
DECLARE @first_key_col sysname --Name of first key column (used for IS NULL check in OUTER JOIN)
DECLARE @declare_cols nvarchar(max) --Column list for table variable DECLARE statement
DECLARE @i_non_key_cols nvarchar(max) --Non-key columns from @i for SELECT
DECLARE @d_non_key_cols nvarchar(max) --Non-key columns from @d for SELECT
DECLARE @select_key_cols nvarchar(max) --List of key columns for SELECT/ISNULL
DECLARE @key_cols nvarchar(max) --List of key columns for DECLARE/INSERT statement
DECLARE @audit_cols nvarchar(max) --Columns to watch/audit

DECLARE @include TABLE (
	colname sysname PRIMARY KEY CLUSTERED
)
IF @include_cols IS NOT NULL
BEGIN
	--Split column names
	DECLARE @split int
	DECLARE @start int
	SET @split = CHARINDEX(',', @include_cols, 0)
	SET @start = 1
	
	WHILE @split != 0
	BEGIN
		INSERT INTO @include (colname)
		SELECT PARSENAME(SUBSTRING(@include_cols, @start, @split-@start), 1)
		EXCEPT
		SELECT colname FROM @include

		SET @start = @split + 1
		SET @split = CHARINDEX(',', @include_cols, @start)
	END

	INSERT INTO @include (colname)
	SELECT PARSENAME(RIGHT(@include_cols, DATALENGTH(@include_cols) / 2 - @start + 1), 1)
	EXCEPT
	SELECT colname FROM @include

	--Check for bad column names
	SET @msg = NULL
	SELECT TOP 1 @msg = (SELECT LTRIM(RTRIM(colname)) FROM @include EXCEPT SELECT name FROM sys.columns WHERE object_id = object_id(ISNULL(QUOTENAME(@table_schema) + '.', '') + QUOTENAME(@table_name)))
	IF @msg IS NOT NULL
	BEGIN
		RAISERROR('Column ''%s'' not found.', 16, 1, @msg)
		RETURN
	END
END

--Create the audit table
SET @sql = 'CREATE TABLE ' + ISNULL(QUOTENAME(@audit_table_schema) + '.', '') + QUOTENAME(@audit_table_name) + ' (' + @crlf

DECLARE @cols TABLE (
	name sysname,
	column_id int PRIMARY KEY CLUSTERED,
	key_ordinal int,
	type_string nvarchar(100),
	include bit NOT NULL DEFAULT 1
)

DECLARE @textcols varchar(max)
SELECT @textcols = ISNULL(@textcols + ', ', '') + c.name
FROM sys.columns c
	LEFT OUTER JOIN @include i
		ON c.name = i.colname
	INNER JOIN sys.types t
		ON c.system_type_id = t.system_type_id
WHERE c.object_id = object_id(ISNULL(QUOTENAME(@table_schema) + '.', '') + QUOTENAME(@table_name))
	AND t.name IN ('text', 'image')
	AND (i.colname IS NOT NULL OR @include_cols IS NULL)

IF @textcols IS NOT NULL
BEGIN
	RAISERROR('Warning: text/image columns cannot be audited and will be ommitted.
%s', 10, 1, @textcols)
END

--Get a list of columns and data types.
INSERT INTO @cols
SELECT
	c.name,
	c.column_id,
	ic.key_ordinal,
	CASE
		WHEN t.name IN ('decimal', 'numeric') THEN t.name + N'(' + CAST(c.precision AS nvarchar) + N',' + CAST(c.scale AS nvarchar) + N')'
		WHEN t.name IN ('float', 'datetime2', 'datetimeoffset', 'time') THEN t.name + N'(' + CAST(c.precision AS nvarchar) + N')'
		WHEN t.name = 'char' AND @force_varchar_varbinary = 1 THEN 'varchar' + N'(' + CAST(c.max_length AS nvarchar) + N')'
		WHEN t.name = 'nchar' AND @force_varchar_varbinary = 1 THEN 'nvarchar' + N'(' + CAST(c.max_length AS nvarchar) + N')'
		WHEN t.name = 'binary' AND @force_varchar_varbinary = 1 THEN 'varbinary' + N'(' + CAST(c.max_length AS nvarchar) + N')'
		WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary') AND c.max_length = -1 THEN t.name + N'(max)'
		WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary') THEN t.name + N'(' + CAST(c.max_length AS nvarchar) + N')'
		ELSE t.name
	END AS type_string,
	1
FROM sys.columns c
	LEFT OUTER JOIN (
		sys.index_columns ic
			INNER JOIN sys.indexes i
				ON ic.object_id = i.object_id
				AND ic.index_id = i.index_id
				AND i.is_primary_key = 1
	)
		ON c.object_id = i.object_id
		AND c.column_id = ic.column_id
		AND ic.is_included_column = 0
	INNER JOIN sys.types t
		ON c.system_type_id = t.system_type_id
WHERE c.object_id = object_id(ISNULL(QUOTENAME(@table_schema) + '.', '') + QUOTENAME(@table_name))
	AND t.name NOT IN ('text', 'image')
ORDER BY column_id

--Exclude anything not in @include_cols
IF @include_cols IS NOT NULL
BEGIN
	UPDATE @cols
	SET include = 0
	WHERE name NOT IN (SELECT LTRIM(RTRIM(colname)) FROM @include WHERE colname IS NOT NULL)
		AND key_ordinal IS NULL
END

--Build column lists for CREATE TABLE and INSERT statements.
SET @c = NULL
SELECT
	@c = ISNULL(@c + ',' + @crlf, '') + QUOTENAME(c.name) + ' ' + c.type_string,
	@c_insert = ISNULL(@c_insert + ', ', '') + QUOTENAME(c.name)
FROM @cols c
WHERE key_ordinal IS NOT NULL
ORDER BY key_ordinal

SET @sql = @sql + @c + ',' + @crlf

SET @c = NULL
SELECT
	@c = ISNULL(@c + ',' + @crlf, '') + QUOTENAME('Old_' + c.name) + ' ' + c.type_string,
	@c_insert = ISNULL(@c_insert + ', ', '') + QUOTENAME('Old_' + c.name)
FROM @cols c
WHERE include = 1
	AND key_ordinal IS NULL
ORDER BY column_id

SET @sql = @sql + @c + ',' + @crlf

SET @c = NULL
SELECT
	@c = ISNULL(@c + ',' + @crlf, '') + QUOTENAME('New_' + c.name) + ' ' + c.type_string,
	@c_insert = ISNULL(@c_insert + ',', '') + QUOTENAME('New_' + c.name)
FROM @cols c
WHERE include = 1
	AND key_ordinal IS NULL
ORDER BY column_id

SET @sql = @sql + @c

SET @c_insert = @c_insert + ', _Action, _Changed, _Changed_By, _Statement, _Application'

SET @sql = @sql + ',
_Action char(1),
_Changed datetime,
_Changed_By nvarchar(128),
_Statement nvarchar(max),
_Application nvarchar(128)
)' + ISNULL(' ON ' + QUOTENAME(@audit_table_filegroup), '')

--PRINT @sql
EXEC sp_executesql @sql

--List key columns for CREATE INDEX/JOIN.
SELECT
	@key_cols_join = ISNULL(@key_cols_join + ' AND ', '') + 'i.' + QUOTENAME(name) + ' = d.' + QUOTENAME(name),
	@key_cols = ISNULL(@key_cols + ', ', '') + QUOTENAME(name)
FROM @cols
WHERE key_ordinal IS NOT NULL
ORDER BY key_ordinal

--Build a clustered index on the audit table using the underlying table's primary key.
SET @sql ='CREATE CLUSTERED INDEX ' + QUOTENAME('CX_' + @audit_table_name) + ' ON ' + ISNULL(QUOTENAME(@audit_table_schema) + '.', '') + QUOTENAME(@audit_table_name) + ' (' + @key_cols + ', _Changed)'

--PRINT @sql
EXEC sp_executesql @sql

SET @sql = 'CREATE NONCLUSTERED INDEX ' + QUOTENAME('IX_' + @audit_table_name + '_Changed') + ' ON ' + ISNULL(QUOTENAME(@audit_table_schema) + '.', '') + QUOTENAME(@audit_table_name) + ' (_Changed)'

EXEC sp_executesql @sql

--Start building the trigger statement.
SET @sql = 'CREATE TRIGGER ' + ISNULL(@audit_trigger_name, QUOTENAME('tr_' + @table_name + '_Audit')) + ' ON ' + ISNULL(QUOTENAME(@table_schema) + '.', '') + QUOTENAME(@table_name) + ' FOR INSERT, UPDATE, DELETE
AS
'

--Columns for the temp tables.
SELECT @declare_cols = ISNULL(@declare_cols + ', ', '') + QUOTENAME(c.name) + ' ' + c.type_string,
	@audit_cols = ISNULL(@audit_cols + ', ', '') + QUOTENAME(c.name)
FROM @cols c
WHERE include = 1
ORDER BY column_id

SELECT TOP 1 @first_key_col = name
FROM @cols
WHERE key_ordinal IS NOT NULL
ORDER BY key_ordinal

SELECT
	@i_non_key_cols = ISNULL(@i_non_key_cols + ', ', '') + 'i.' + QUOTENAME(name),
	@d_non_key_cols = ISNULL(@d_non_key_cols + ', ', '') + 'd.' + QUOTENAME(name)
FROM @cols
WHERE key_ordinal IS NULL
	AND include = 1
ORDER BY column_id

SELECT
	@select_key_cols = ISNULL(@select_key_cols + ', ', '') + 'ISNULL(i.' + QUOTENAME(name) + ', d.' + QUOTENAME(name) + ')'
FROM @cols
WHERE key_ordinal IS NOT NULL
ORDER BY key_ordinal

--Trigger body
SET @sql = @sql + 'DECLARE @i TABLE (' + @declare_cols + ', PRIMARY KEY CLUSTERED (' + @key_cols + '))
INSERT INTO @i SELECT ' + @audit_cols + ' FROM INSERTED

DECLARE @d TABLE (' + @declare_cols + ', PRIMARY KEY CLUSTERED (' + @key_cols + '))
INSERT INTO @d SELECT ' + @audit_cols + ' FROM DELETED

IF EXISTS (SELECT TOP 1 1 FROM @i i FULL OUTER JOIN @d d ON ' + @key_cols_join + ' WHERE EXISTS (SELECT i.* EXCEPT SELECT d.*) OR EXISTS (SELECT d.* EXCEPT SELECT i.*))
BEGIN
	--Get the SQL statement that caused the update
	DECLARE @sql nvarchar(max)
	SET @sql = ''DBCC INPUTBUFFER('' + CAST(@@SPID AS nvarchar(100)) + '')''
	CREATE TABLE #SQL (
		EventType varchar(100),
		Parameters int,
		EventInfo nvarchar(max)
	)
	INSERT INTO #SQL
	EXEC sp_executesql @sql
	
	SELECT @sql = EventInfo FROM #SQL
	DROP TABLE #SQL

	INSERT INTO ' + ISNULL(QUOTENAME(@audit_table_schema) + '.', '') + QUOTENAME(@audit_table_name) + ' (
		' + @c_insert + '
	)
	SELECT ' + @select_key_cols + ',
		' + @d_non_key_cols + ',
		' + @i_non_key_cols + ',
		CASE
			WHEN i.' + QUOTENAME(@first_key_col) + ' IS NULL THEN ''D''
			WHEN d.' + QUOTENAME(@first_key_col) + ' IS NULL THEN ''I''
			ELSE ''U''
		END AS Action,
		GETDATE() AS Changed,
		SUSER_SNAME() AS Changed_By,
		@sql AS Statement,
		APP_NAME() AS Application
	FROM @i i
		FULL OUTER JOIN @d d
			ON ' + @key_cols_join + '
	WHERE EXISTS (SELECT i.* EXCEPT SELECT d.*)
		OR EXISTS (SELECT d.* EXCEPT SELECT i.*)
END
'

--PRINT @sql
EXEC sp_executesql @sql
GO

--Mark as a system object. This allows calling spCreateDataAudit from any database.
EXEC sp_MS_MarkSystemObject 'sp_CreateDataAudit'
