Sql Server - Cria/atualiza uma tabela de log e sua trigger no schema especificado. O a tabela insere um registro sempre que há alteração/deleção na original.

_LOGATUALIZARESTRUTURA.sql

-- _LOGATUALIZARESTRUTURA 'dbo', NULL

ALTER PROCEDURE _LOGATUALIZARESTRUTURA(@SCHEMA VARCHAR(20), @TABLE VARCHAR(20))
AS
BEGIN
	/*
	tipos a usar no sql server
	- varchar (texto)
	- int (máx 2.147.483.647) 4bytes
	- tinyint (campos de tipo, máx 255) 1byte
	- decimal(9,2) (moeda 2 dígitos de centavo max 9.999.999,99) 5bytes
	- decimal(19,2) (decimal maior que 9,2) 9bytes (9 bytes se maior que 9,2 não compensa diminuir)
	- date (campos data sem hora) 3bytes
	- datetime2(0) (campos data e hora, recomendado ISO sql) 6bytes
	*/

	SET NOCOUNT ON;
	
	-- alterar para schema (deve funcionar com outro banco: 'bancolog.dbo', DESLIGAR CREATE SCHEMA)
	DECLARE @SCHEMALOG VARCHAR(5) = 'log'; 
	DECLARE @SQL NVARCHAR(MAX);
	
	-- CRIA O SCHEMA DE LOG SE NAO EXISTE
	IF (NOT EXISTS(SELECT 1 FROM SYS.SCHEMAS S WHERE S.NAME = @SCHEMALOG))
	BEGIN
		SET @SQL = CONCAT('CREATE SCHEMA ', @SCHEMALOG, ';');
		EXEC sys.sp_executesql @SQL;
	END
	

	CREATE TABLE #TABELAS (
		TABELAID INT NOT NULL IDENTITY PRIMARY KEY,
		-- o nome da tabela não está seguro com [] (ESPAÇO EM NOME DE TABELA???)
		NOME VARCHAR(100) NOT NULL,
		NOMELOG VARCHAR(100) NOT NULL,
		EXISTE BIT NOT NULL,
		CAMPOS VARCHAR(MAX),
		CAMPOSTIPOS VARCHAR(MAX),
		CAMPOSADD VARCHAR(MAX),
		CAMPOSALTER VARCHAR(MAX),
		SQLCREATE VARCHAR(MAX),
		SQLADD VARCHAR(MAX),
		SQLALTER VARCHAR(MAX),
		SQLTRIGGER VARCHAR(MAX)
	);

	INSERT INTO #TABELAS (NOME, NOMELOG, EXISTE, SQLCREATE, SQLADD, SQLALTER, SQLTRIGGER)
	SELECT CONCAT(S.NAME, '.', T.NAME), 
		CONCAT(@SCHEMALOG, '.', T.NAME), 
		IIF(OBJECT_ID(CONCAT(@SCHEMALOG, '.', T.NAME)) IS NULL, 0, 1), 
		
		-- 0=TABELA DE LOG; 1=CAMPOS COM TIPOS
		CONCAT('CREATE TABLE {0} ({1}, ', CHAR(13),
		'LogId int NOT NULL IDENTITY(1,1) PRIMARY KEY, ', CHAR(13),
		'LogData datetime2(0) NOT NULL);'),
		
		-- 0=TABELA DE LOG; 1=CAMPOS ADD
		'ALTER TABLE {0} ADD {1};',
		
		-- 0=TABELA DE LOG; 1=CAMPOS ALTER
		'ALTER TABLE {0} ALTER COLUMN {1};',
		
		-- 0=TABELA ORIGINAL; 1=TABELA DE LOG; 2=CAMPOS
		CONCAT('CREATE OR ALTER TRIGGER {0}LogT ON {0} AFTER UPDATE, DELETE AS', CHAR(13),
		'SET NOCOUNT ON;', CHAR(13),
		'INSERT INTO {1} ({2}, LogData)', CHAR(13),
		'SELECT {2}, GETDATE() FROM DELETED;')
	FROM SYS.TABLES T
	JOIN SYS.SCHEMAS S ON S.SCHEMA_ID = T.SCHEMA_ID
	WHERE (S.NAME = @SCHEMA)
	AND (@TABLE IS NULL OR T.OBJECT_ID = OBJECT_ID(@TABLE))
	ORDER BY T.NAME;

	-- CARREGA CAMPOS DAS TABELAS
	UPDATE TT
	SET CAMPOS = (SELECT CONCAT('[', C.NAME, '], ') 
	              FROM SYS.COLUMNS C
				  JOIN SYS.TYPES T ON T.USER_TYPE_ID = C.USER_TYPE_ID
				  WHERE C.OBJECT_ID = OBJECT_ID(TT.NOME)
				  FOR XML PATH('')),
				  
	CAMPOSTIPOS = (SELECT CONCAT('[', C.NAME, '] ', 
				      T.NAME, 
					  CASE 
					  WHEN T.NAME IN ('varchar', 'char', 'varbinary', 'binary', 'text') 
					       THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH AS VARCHAR(5))), ')')   
					  WHEN T.NAME IN ('nvarchar', 'nchar', 'ntext') 
					       THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH / 2 AS VARCHAR(5))), ')')
					  WHEN T.NAME IN ('datetime2', 'time2', 'datetimeoffset') 
					       THEN CONCAT('(', C.SCALE, ')')						   
					  WHEN T.NAME IN ('decimal', 'float') 
					       THEN CONCAT('(', C.PRECISION, ',', C.SCALE, ')')
					  ELSE ''
					  END,
					  ', ') 
	              FROM SYS.COLUMNS C
				  JOIN SYS.TYPES T ON T.USER_TYPE_ID = C.USER_TYPE_ID
				  WHERE C.OBJECT_ID = OBJECT_ID(TT.NOME)
				  FOR XML PATH('')),
				  
	CAMPOSADD = (SELECT CONCAT('[', C.NAME, '] ', 
				      T.NAME, 
					  CASE 
					  WHEN T.NAME IN ('varchar', 'char', 'varbinary', 'binary', 'text') 
					       THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH AS VARCHAR(5))), ')')   
					  WHEN T.NAME IN ('nvarchar', 'nchar', 'ntext') 
					       THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH / 2 AS VARCHAR(5))), ')')
					  WHEN T.NAME IN ('datetime2', 'time2', 'datetimeoffset') 
					       THEN CONCAT('(', C.SCALE, ')')						   
					  WHEN T.NAME IN ('decimal', 'float') 
					       THEN CONCAT('(', C.PRECISION, ',', C.SCALE, ')')
					  ELSE ''
					  END,
					  ', ') 
	              FROM SYS.COLUMNS C
				  JOIN SYS.TYPES T ON T.USER_TYPE_ID = C.USER_TYPE_ID
				  LEFT JOIN SYS.COLUMNS JA ON JA.NAME = C.NAME AND JA.OBJECT_ID = OBJECT_ID(TT.NOMELOG)
				  WHERE C.OBJECT_ID = OBJECT_ID(TT.NOME)
				  AND (JA.COLUMN_ID IS NULL)
				  FOR XML PATH('')),
				  
	CAMPOSALTER = (SELECT CONCAT('[', C.NAME, '] ', 
				      T.NAME, 
					  C.MAX_LENGTH,
					  CASE 
					  WHEN T.NAME IN ('varchar', 'char', 'varbinary', 'binary', 'text') 
					       THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH AS VARCHAR(5))), ')')   
					  WHEN T.NAME IN ('nvarchar', 'nchar', 'ntext') 
					       THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH / 2 AS VARCHAR(5))), ')')
					  WHEN T.NAME IN ('datetime2', 'time2', 'datetimeoffset') 
					       THEN CONCAT('(', C.SCALE, ')')						   
					  WHEN T.NAME IN ('decimal', 'float') 
					       THEN CONCAT('(', C.PRECISION, ',', C.SCALE, ')')
					  ELSE ''
					  END,
					  ', ') 
	              FROM SYS.COLUMNS C
				  JOIN SYS.TYPES T ON T.USER_TYPE_ID = C.USER_TYPE_ID
				  LEFT JOIN SYS.COLUMNS JA ON JA.NAME = C.NAME AND JA.OBJECT_ID = OBJECT_ID(TT.NOMELOG)
				  WHERE C.OBJECT_ID = OBJECT_ID(TT.NOME)
				  AND (JA.COLUMN_ID IS NOT NULL AND C.MAX_LENGTH > JA.MAX_LENGTH)
				  FOR XML PATH(''))
	FROM #TABELAS TT;
	
	-- TIRA A ULTIMA VIRGULA DOS CAMPOS
	UPDATE #TABELAS
	SET CAMPOS = SUBSTRING(CAMPOS, 1, LEN(CAMPOS) - 1),
	CAMPOSADD = SUBSTRING(CAMPOSADD, 1, LEN(CAMPOSADD) - 1),
	CAMPOSALTER = SUBSTRING(CAMPOSALTER, 1, LEN(CAMPOSALTER) - 1),
	CAMPOSTIPOS = SUBSTRING(CAMPOSTIPOS, 1, LEN(CAMPOSTIPOS) - 1);
	
	-- SQLCREATE: 0=TABELA DE LOG; 1=CAMPOS COM TIPOS
	-- SQLADD: 0=TABELA DE LOG; 1=CAMPOS ADD
	-- SQLTRIGGER: 0=TABELA ORIGINAL; 1=TABELA DE LOG; 2=CAMPOS
	UPDATE #TABELAS
	SET SQLCREATE = REPLACE(SQLCREATE, '{0}', NOMELOG),
	SQLADD = REPLACE(SQLADD, '{0}', NOMELOG),
	SQLALTER = REPLACE(SQLALTER, '{0}', NOMELOG),
	SQLTRIGGER = REPLACE(SQLTRIGGER, '{0}', NOME);

	UPDATE #TABELAS
	SET SQLCREATE = REPLACE(SQLCREATE, '{1}', CAMPOSTIPOS),
	SQLADD = REPLACE(SQLADD, '{1}', CAMPOSADD),
	SQLALTER = REPLACE(SQLALTER, '{1}', CAMPOSALTER),
	SQLTRIGGER = REPLACE(SQLTRIGGER, '{1}', NOMELOG);
	
	UPDATE #TABELAS
	SET SQLTRIGGER = REPLACE(SQLTRIGGER, '{2}', CAMPOS);
	
	UPDATE #TABELAS
	SET SQLCREATE = IIF(EXISTE = 1, NULL, SQLCREATE),
	SQLADD = IIF(EXISTE = 0, NULL, SQLADD),
	SQLALTER = IIF(EXISTE = 0, NULL, SQLALTER),
	SQLTRIGGER = IIF(EXISTE = 1 AND CAMPOSADD IS NULL, NULL, SQLTRIGGER);
	
--	SELECT * 
--	FROM #TABELAS;	
--	RETURN;

	CREATE TABLE #SAIDA (
		ID INT NOT NULL IDENTITY PRIMARY KEY, 
		TABELA VARCHAR(100),
		ERRO VARCHAR(MAX),
		DATAHORA DATETIME2(0)
	);

	DECLARE @NOMETABELA VARCHAR(50),
		@SQLCREATE NVARCHAR(MAX), 
		@SQLADD NVARCHAR(MAX), 
		@SQLALTER NVARCHAR(MAX), 
		@SQLTRIGGER NVARCHAR(MAX);
	
	DECLARE dbcursor CURSOR FOR 
	SELECT T.NOME, T.SQLCREATE, T.SQLADD, T.SQLALTER, T.SQLTRIGGER
	FROM #TABELAS T;
	
	OPEN dbcursor
	FETCH NEXT FROM dbcursor INTO @NOMETABELA, @SQLCREATE, @SQLADD, @SQLALTER, @SQLTRIGGER;
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			SET @SQL = @SQLCREATE;
			IF (@SQL IS NOT NULL) EXEC sys.sp_executesql @SQL;
			
			SET @SQL = @SQLADD;
			IF (@SQL IS NOT NULL) EXEC sys.sp_executesql @SQL;
			
			SET @SQL = @SQLALTER;
			IF (@SQL IS NOT NULL) EXEC sys.sp_executesql @SQL;
			
			SET @SQL = @SQLTRIGGER;
			IF (@SQL IS NOT NULL) EXEC sys.sp_executesql @SQL;
		
			IF (@SQLCREATE IS NOT NULL OR @SQLADD IS NOT NULL 
				OR @SQLALTER IS NOT NULL OR @SQLTRIGGER IS NOT NULL)
			BEGIN
				INSERT INTO #SAIDA (TABELA, DATAHORA) VALUES (@NOMETABELA, GETDATE());
			END
		END TRY
		BEGIN CATCH
			INSERT INTO #SAIDA (TABELA, ERRO, DATAHORA) 
			VALUES (@NOMETABELA, CONCAT(ERROR_NUMBER(), ' - ', ERROR_MESSAGE(), CHAR(13), @SQL), GETDATE());
		END CATCH
		
		FETCH NEXT FROM dbcursor INTO @NOMETABELA, @SQLCREATE, @SQLADD, @SQLALTER, @SQLTRIGGER;
	END
	
	CLOSE dbcursor;
	DEALLOCATE dbcursor;
	
	SELECT TABELA, ERRO, DATAHORA FROM #SAIDA;
	
END

Esta solução de log/auditoria de tabelas foi criada como paliativa mas tende a funcionar bem em
ambientes de produção já que provavelmente terá pouco impacto na performance.

A procedure cria uma tabela com os mesmos campos da original, e adiciona campos novos
quando executada e a tabela já existir. Campos que tiverem seu tamanho aumentado também
são alterados.

Além da tabela, é criada uma trigger na tabela original que insere um registro na tabela de log
a cada update/delete com o estado dos campos antes da alteração mais um campo de data/hora atual.

Uma recomendação é ter um campo com referência ao usuário na tabela original, que a aplicação
atualize em toda inserção/alteração, para que seja possível rastrear.