Database Record Change Tracker

A Java utility for tracking created and updated records in a SQL Server database by monitoring timestamp fields.

Features

• Automatically discovers tables with timestamp tracking columns across all schemas
• Handles case variations in timestamp column names (CREATE_TS, Update_Ts, etc.)
• Accounts for small timing differences (0.1 second tolerance) between CREATE_TS and UPDATE_TS during record creation
• Provides detailed logging of the scanning process
• Groups results by table and separates created vs updated records

Configuration

Create a config.properties file in src/main/resources with:

# Database Connection
db.url=jdbc:sqlserver://your-server:1433;databaseName=your-database
db.user=your-username
db.password=your-password

# Time Range to Scan (format: YYYY-MM-DD HH:mm:ss.SSS)
start.timestamp=2025-07-15 14:59:00.000
end.timestamp=2025-07-15 15:00:00.000

Building and Running

1. Build the project:

   mvn clean package
   

2. Run the scanner:

   mvn exec:java
   

How It Works

1. The scanner first discovers all tables that have timestamp tracking columns (CREATE_TS and UPDATE_TS)
2. For each table found, it queries for records where UPDATE_TS falls within the specified time range
3. Records are classified as:
   • Created: If CREATE_TS and UPDATE_TS are equal (within 0.1 second tolerance)
   • Updated: If UPDATE_TS is different from CREATE_TS

Output Format

The output shows results grouped by table:

[schema.table_name]
  Created: N
    - {column1=value1, column2=value2, ...}
    - {column1=value1, column2=value2, ...}
  Updated: M
    - {column1=value1, column2=value2, ...}
    - {column1=value1, column2=value2, ...}

Notes

• System schemas (sys, INFORMATION_SCHEMA) are excluded from scanning
• A 0.1 second tolerance is used when comparing CREATE_TS and UPDATE_TS to account for small system delays
• All timestamp comparisons are case-insensitive to handle different column naming conventions