ChangeLog

ChangeLog.sql

USE ChangeLog_test
GO
--DROP TRIGGER
DROP TRIGGER tr_MScdc_ddl_event ON DATABASE
--disable CDC and DROP DDL TABLE
EXEC sys.sp_cdc_disable_db
GO
--enable CDC
EXEC sys.sp_cdc_enable_db
GO
--DDL
CREATE TABLE cdc.ddl_event(
	id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_cdc_ddl_event_id PRIMARY KEY,
	key_event UNIQUEIDENTIFIER NOT NULL,
	datetime DATETIME2,
	event_type SYSNAME,
	object_type SYSNAME,
	object_name SYSNAME,
	data XML
)
CREATE TABLE cdc.colums_history(
	id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_cdc_colums_history_id PRIMARY KEY,
	key_event UNIQUEIDENTIFIER NOT NULL,
	TABLE_SCHEMA SYSNAME,
	TABLE_NAME SYSNAME,
	COLUMN_NAME SYSNAME,
	ORDINAL_POSITION INT,
	COLUMN_DEFAULT NVARCHAR(4000),
	IS_NULLABLE VARCHAR(3),
	DATA_TYPE NVARCHAR(128),
	CHARACTER_MAXIMUM_LENGTH INT,
	CHARACTER_OCTET_LENGTH INT,
	NUMERIC_PRECISION TINYINT,
	NUMERIC_PRECISION_RADIX SMALLINT,
	NUMERIC_SCALE INT,
	DATETIME_PRECISION SMALLINT
)
--cdc enable all table
DECLARE @name VARCHAR(128), @key_event UNIQUEIDENTIFIER = CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER)
DECLARE line CURSOR
FOR
	SELECT 
		TABLE_NAME
	FROM INFORMATION_SCHEMA.TABLES AS tab
	LEFT JOIN cdc.change_tables AS ch
	ON ISNULL(OBJECT_ID(tab.TABLE_NAME), 0) = ch.source_object_id
	WHERE TABLE_SCHEMA = 'dbo' AND NOT TABLE_NAME = 'systranschemas' AND ch.object_id IS NULL
OPEN line
FETCH NEXT FROM line INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
	--активация cdc
	DECLARE @change_table SYSNAME = @name + '_' + REPLACE(NEWID(), '-', '')
	EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = @name, @role_name = NULL, @capture_instance = @change_table
	--сохранение описания таблицы
	INSERT INTO cdc.colums_history 
	SELECT @key_event, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION
		FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @name
FETCH NEXT FROM line INTO @name
END
CLOSE line
DEALLOCATE line
-- ALL event
IF EXISTS(SELECT object_id FROM sys.triggers WHERE name = 'tr_MScdc_ddl_event')
	DROP TRIGGER tr_MScdc_ddl_event ON DATABASE
GO
CREATE TRIGGER tr_MScdc_ddl_event
	ON DATABASE
	FOR DDL_DATABASE_LEVEL_EVENTS
	AS
BEGIN
	
	--инициализация общих переменных
	DECLARE @key_event UNIQUEIDENTIFIER = NEWID()
	DECLARE @event XML = EVENTDATA()
	DECLARE @event_type SYSNAME = @event.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)')
	DECLARE @object_schema SYSNAME = @event.value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME')
	DECLARE @object_type SYSNAME = @event.value('(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME')
	DECLARE @object_name SYSNAME = @event.value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
	
	--запись всех событий
	INSERT INTO cdc.ddl_event VALUES
	(@key_event, SYSDATETIME(), @event_type, @object_type, @object_name, @event)

	IF (@object_schema = 'dbo')
	BEGIN
		
		--инициализация переменных
		DECLARE @table_main SYSNAME
		IF RIGHT(@object_name, 2) = 'NG'
			SET @table_main = LEFT(@object_name, LEN(@object_name)-2)
		ELSE IF RIGHT(@object_name, 1) = 'Z'
			SET @table_main = LEFT(@object_name, LEN(@object_name)-1)
		ELSE
			SET @table_main = @object_name
		DECLARE @change_table SYSNAME = @table_main + '_' + REPLACE(NEWID(), '-', '')

		IF (@event_type IN ('CREATE_TABLE','ALTER_TABLE'))
		BEGIN
			--активация cdc для новой таблицы
			IF (NOT (LEFT(@object_name, 8) = '_AccRgAT' OR LEFT(@object_name, 10) = '_AccumRgTn' OR LEFT(@object_name, 9) = '_AccumRgT')) --исключение, возникает конфликт
				EXEC sys.sp_cdc_enable_table @source_schema = @object_schema, @source_name = @object_name, @role_name = NULL, @capture_instance = @change_table
			--сохранение описания таблицы
			INSERT INTO cdc.colums_history 
			SELECT @key_event, TABLE_SCHEMA, @table_main, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION
				FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @object_name
		END
		
		/*--переносим изменения, если это реструктуризация (изменение таблицы)
		IF (@event_type IN ('DROP_TABLE','ALTER_TABLE'))
		BEGIN
			DECLARE @change_table_old SYSNAME
			SELECT TOP 1 @change_table_old = capture_instance FROM cdc.change_tables WHERE NOT capture_instance = @change_table ORDER BY create_date DESC
			IF (@change_table_old IS NOT NULL)
				EXEC('INSERT INTO cdc.' + @change_table + '_CT SELECT * FROM cdc.' + @change_table_old + '_CT')
		END
		*/
		--триггер cdc
		DECLARE @drop_table BIT = 0
		IF (@event_type IN('ALTER_TABLE', 'DROP_TABLE'))
		BEGIN
			SET @drop_table = 0
		END
		IF (@drop_table = 1 OR @event_type IN('ALTER_INDEX', 'DROP_INDEX'))
		BEGIN
			SET ANSI_NULLS ON
			SET ANSI_PADDING ON
			SET ANSI_WARNINGS ON
			SET ARITHABORT ON
			SET CONCAT_NULL_YIELDS_NULL ON
			SET NUMERIC_ROUNDABORT OFF
			SET QUOTED_IDENTIFIER ON
			EXEC sys.sp_MScdc_ddl_event @event
		END
	END
END

ChangeLog_read.sql

USE ChangeLog_test
GO
DECLARE @tables_empty TABLE (object_id INT, empty BIT)
DECLARE @object_id INT, @capture_instance SYSNAME, @command NVARCHAR(150), @empty BIT
DECLARE line CURSOR 
FOR SELECT object_id, capture_instance FROM cdc.change_tables
OPEN line
FETCH NEXT FROM line INTO @object_id, @capture_instance
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @empty = 0;
	SET @command = 'SELECT TOP 1 @e = 1 FROM cdc.' + @capture_instance + '_CT'
	EXEC sp_executesql @command, N'@e BIT OUTPUT', @e = @empty OUTPUT
	INSERT INTO @tables_empty VALUES(@object_id, @empty)

FETCH NEXT FROM line INTO @object_id, @capture_instance
END
CLOSE line
DEALLOCATE line

SELECT 
	ISNULL(obj.name, LEFT(ch.capture_instance, LEN(ch.capture_instance)-33)) AS name,
	te.empty,
	ch.create_date,
	col.TABLE_NAME,
	col.COLUMN_NAME, 
	col.ORDINAL_POSITION,
	col.COLUMN_DEFAULT,
	col.IS_NULLABLE,
	col.DATA_TYPE,
	col.CHARACTER_MAXIMUM_LENGTH,
	col.CHARACTER_OCTET_LENGTH,
	col.NUMERIC_PRECISION,
	col.NUMERIC_PRECISION_RADIX,
	col.NUMERIC_SCALE,
	col.DATETIME_PRECISION

FROM cdc.change_tables AS ch
LEFT JOIN sys.objects AS obj
ON ch.source_object_id = obj.object_id
LEFT JOIN @tables_empty AS te
ON ch.object_id = te.object_id
LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS col
ON ch.capture_instance = LEFT(col.TABLE_NAME, LEN(col.TABLE_NAME)-3)
AND NOT LEFT(COLUMN_NAME, 3) = '__$'

where te.empty = 1
ORDER BY
	ISNULL(obj.name, LEFT(ch.capture_instance, LEN(ch.capture_instance)-33)),
	ch.create_date DESC

sql_audit.sql

USE [ChangeLog_test]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20220521-100019]
FOR SERVER AUDIT [Audit-20220521-083853]

ADD (DELETE ON SCHEMA::[dbo] BY [dbo]),
ADD (EXECUTE ON SCHEMA::[dbo] BY [dbo]),
ADD (INSERT ON SCHEMA::[dbo] BY [dbo]),
ADD (SELECT ON SCHEMA::[dbo] BY [dbo]),
ADD (UPDATE ON SCHEMA::[dbo] BY [dbo]),

ADD (DBCC_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OPERATION_GROUP)

WITH (STATE = OFF)
GO


SELECT * FROM sys.fn_get_audit_file ('D:\test_audit\Audit-20220521-083853_4F9543FD-1FC6-41B1-BBD9-042551C98A56_0_132975916003240000.sqlaudit',default,default);
GO