Monday, January 30, 2017

Home programming projects: data browser and differences finder

During this temporary work hiatus, I am spending some time working on enhancing a couple of my "Winter break" projects. These are projects that I worked on for fun during past Winter breaks from work and which have been useful in my day-to-day work.

1) A database "browser", where you search for a record and can then browse forward and backward in the database by following foreign key entries.

2) A database difference finder which allows you to take a snapshot of a database's schema and latest record info and then compare to other such snapshots

The Database Browser

The purpose here is to give a quick and handy browsing ability. This means that you select a record somewhere in the database (schema info loaded when server and database selected) and can then browse back to records in other tables that have a key in that record...and forward to records containing the "current" records key.

Most windows have "Show SQL" buttons that show you the SQL used to generate those records.

The "Get Field Info or SQL Join" button takes you to a popup which serves two functions.

1) It can search for tables, stored procs and triggers that contain a string you want to locate

2) It can generate a basic SQL join between two tables that you name

Database difference finder

The purpose of this program is to find differences between snapshots of two databases or the same database after a change has been made (structural or in terms of saved data).

Here, I am showing the differences detected when a tables has had a column added between two snapshots.

In this case, I am showing the differences resulting when a database row has been added, deleted or changed (based on timestamp differences).