Generating a log report of each table in a SQL Server database involves creating a mechanism to track changes (inserts, updates, deletes) on each table. This can be achieved using several methods, including:
1. Change Data Capture (CDC)
CDC is a feature in SQL Server that provides an easy way to track changes. Here’s how to set it up:
Enable CDC on the Database
USE [YourDatabaseName];
GO
EXEC sys.sp_cdc_enable_db;
GO
Enable CDC on Each Table
USE [YourDatabaseName];
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo', -- Replace with your schema
@source_name = N'YourTableName', -- Replace with your table name
@role_name = NULL;
GO
Query CDC Logs
CDC creates tables with the captured changes. You can query these tables for logs:
SELECT *
FROM cdc.dbo_YourTableName_CT; -- Change 'dbo' and 'YourTableName' accordingly
2. Triggers
Using triggers is another method to log changes to each table. You can create insert, update, and delete triggers to log changes into a separate log table.
Create Log Table
CREATE TABLE ChangeLog (
LogID INT IDENTITY(1,1) PRIMARY KEY,
TableName NVARCHAR(255),
ChangeType NVARCHAR(10),
ChangeDate DATETIME DEFAULT GETDATE(),
ChangedData NVARCHAR(MAX)
);
Create Trigger for Insert
CREATE TRIGGER trgAfterInsert
ON YourTableName
AFTER INSERT
AS
BEGIN
DECLARE @Change NVARCHAR(MAX);
SELECT @Change = (
SELECT * FROM inserted
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');
INSERT INTO ChangeLog (TableName, ChangeType, ChangedData)
VALUES ('YourTableName', 'INSERT', @Change);
END;
Create Trigger for Update
CREATE TRIGGER trgAfterUpdate
ON YourTableName
AFTER UPDATE
AS
BEGIN
DECLARE @Change NVARCHAR(MAX);
SELECT @Change = (
SELECT * FROM inserted
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');
INSERT INTO ChangeLog (TableName, ChangeType, ChangedData)
VALUES ('YourTableName', 'UPDATE', @Change);
END;
Create Trigger for Delete
CREATE TRIGGER trgAfterDelete
ON YourTableName
AFTER DELETE
AS
BEGIN
DECLARE @Change NVARCHAR(MAX);
SELECT @Change = (
SELECT * FROM deleted
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');
INSERT INTO ChangeLog (TableName, ChangeType, ChangedData)
VALUES ('YourTableName', 'DELETE', @Change);
END;
3. SQL Server Audit
SQL Server Audit is another feature that allows tracking of events at the server and database levels.
Create Server Audit
CREATE SERVER AUDIT [AuditName]
TO FILE ( FILEPATH = 'C:\AuditLogs\' );
ALTER SERVER AUDIT [AuditName] WITH (STATE = ON);
Create Database Audit Specification
USE [YourDatabaseName];
GO
CREATE DATABASE AUDIT SPECIFICATION [AuditSpecificationName]
FOR SERVER AUDIT [AuditName]
ADD (INSERT ON OBJECT::[dbo].[YourTableName] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[YourTableName] BY [public]),
ADD (DELETE ON OBJECT::[dbo].[YourTableName] BY [public])
WITH (STATE = ON);
GO
4. SQL Server Profiler
For short-term logging, you can use SQL Server Profiler to trace activities on your database. This is more of a real-time monitoring tool and less of a long-term logging solution.
Querying the Log Report
To query the log report generated by triggers or CDC, you can use a simple SELECT statement:
SELECT * FROM ChangeLog ORDER BY ChangeDate DESC;
This will provide you with a log of changes for each table that has been configured to log changes.
By setting up any of these methods, you can effectively generate and manage log reports for each table in your database.
