How to generate log report of each table from database

 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.