SQLServer Dynamic Revision Table DDL Generator

gistfile1.sql

CREATE FUNCTION dbo.genRevTableColumnsDDL( @TableName AS VARCHAR(40) ) RETURNS VARCHAR(MAX)
AS
BEGIN 
	DECLARE @sql NVARCHAR(MAX) = N'';
	SELECT @sql += CASE
				WHEN column_ordinal = 1 AND UPPER(name) = 'ID' THEN
					CHAR(9) + '[ID]'+ CHAR(9) + 'BIGINT' +  CHAR(9) + 'NOT NULL PRIMARY KEY IDENTITY,' + CHAR(13) + CHAR(10) +
					CHAR(9) + '['+@TableName+'ID]'+ CHAR(9) + 'BIGINT' +  CHAR(9) + 'NOT NULL,' + CHAR(13) + CHAR(10)
				ELSE
					CHAR(9) + '[' + name + '] ' + system_type_name + ' NULL,'+ CHAR(13) + CHAR(10) 
			END
	FROM sys.dm_exec_describe_first_result_set('select * from dbo.['+@TableName+']', NULL, 1)
	RETURN @sql + CHAR(9) + '[operation] CHAR(1) NOT NULL,' + CHAR(13) + CHAR(10)
				+ CHAR(9) + '[updated] DATETIME NOT NULL DEFAULT GetDate(),'+ CHAR(13) + CHAR(10)
				+ CHAR(9) + '[updatedby] VARCHAR(100) NOT NULL DEFAULT CURRENT_USER'
END

CREATE FUNCTION dbo.genCreateRevTableDDL( @TableName AS VARCHAR(4000) ) RETURNS VARCHAR(4000)
AS
BEGIN 
    RETURN 'CREATE TABLE ' + @TableName + 'Rev (' + CHAR(13) + dbo.genRevTableColumnsDDL( @TableName ) + ')'
END

CREATE FUNCTION dbo.getColumnNames (@TableName as nvarchar(50)) RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @sql varchar(MAX) = N'';
	SELECT @sql += name + ',' from sys.columns 
			WHERE object_id = OBJECT_ID(@TableName) AND UPPER(name) != 'ID' AND UPPER(name) != UPPER(@TableName) + 'ID'
	RETURN LEFT(@sql, LEN(@sql)-1)
END

CREATE FUNCTION dbo.getPrefixedColumnNames (@TableName as nvarchar(50), @Prefix as varchar(50)) RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @sql varchar(MAX) = N'';
	SELECT @sql += @Prefix + '.' + name + ',' from sys.columns 
			WHERE object_id = OBJECT_ID(@TableName) AND UPPER(name) != 'ID' AND UPPER(name) != UPPER(@TableName) + 'ID'
	RETURN LEFT(@sql, LEN(@sql)-1)
END

CREATE FUNCTION dbo.genRevTableTriggerDDL( @TableName AS VARCHAR(40) ) RETURNS VARCHAR(MAX)
AS 
BEGIN
	DECLARE @sql varchar(MAX) = 'CREATE TRIGGER tr_' + @TableName + '_rev
ON ['+ @TableName+']
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
	IF EXISTS(SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED)
	BEGIN
		INSERT INTO ['+@TableName+'Rev]('+@TableName+'ID,'+dbo.getColumnNames(@TableName)+',operation, updated, updatedby) SELECT inserted.ID, '+dbo.getPrefixedColumnNames(@TableName,'inserted')+',''u'', GetDate(), SYSTEM_USER FROM INSERTED
	END	

	IF EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS(SELECT * FROM DELETED)
	BEGIN
		INSERT INTO ['+@TableName+'Rev]('+@TableName+'ID,'+dbo.getColumnNames(@TableName)+',operation, updated, updatedby) SELECT inserted.ID, '+dbo.getPrefixedColumnNames(@TableName,'inserted')+',''i'', GetDate(), SYSTEM_USER FROM INSERTED
	END

	IF EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
	BEGIN
		INSERT INTO ['+@TableName+'Rev]('+@TableName+'ID,'+dbo.getColumnNames(@TableName)+',operation, updated, updatedby) SELECT deleted.ID, '+dbo.getPrefixedColumnNames(@TableName,'deleted')+',''d'', GetDate(), SYSTEM_USER FROM DELETED 
	END
END';
	RETURN @sql
END

--Test case example 
CREATE TABLE dbo.[User] ( ID BIGINT, name Varchar(50), telephone Varchar(50))
PRINT dbo.genCreateRevTableDDL('User')
PRINT dbo.genRevTableTriggerDDL('User')
-- End Test case example