Compare database tables

[JustineB]

Hi!
I have not got much experience using VB so wil appreciate any help that I can get!

I have a database that has changes made to its structure almost daily. These changes can include fields added to tables, fields deleted from tables, datatype changes, field name changes etc.

I would like to write something that I can run each week to see what changes have been made in each table. This will help with report writing which is an ongoing project. Currently, we have to run each report that has been written (in Cognos and Crystal Reports) and verify the database. This tell us which tables have been changed, but not what changes have been made to them.
If anyone can help - either with some sample code, or with ideas of how to best go about this, I would appreciate it very much.

Justine.

 

[mymou]

Hi JustineB

Never seen a db that changes daily. Sounds like it might be badly designed - or really exciting!!

Why not have a query that runs through the field collection and writes this information to a table. The query on this table to show changes would be a bit weird - but I guess it can be done.

Stew "Even a stopped clock tells the right time twice a day."

 

[JustineB]

Thanks for your help!
The database (well - datamart) is currently being designed and we are having to write reports for a BI solution. It is rather difficult to do as the changes are so frequent, but we need to keep working so we have to live with it!

 

[mymou]

Hi Justine

I know nothing about datamart, but for most dbs this is very unusual.

What would be more acceptable - would be to have two fields - one with data and another with a code (that specifies data type - like field names in your case).

I have done too much BI work and I don't think I would like to be in your position.

Good Luck

Stew
"Even a stopped clock tells the right time twice a day."

 

[MichaelRed]

JustineB,

You have my sympathy. I would advise you -and your coworkers- to jointly approach the "powers that be" and ask they reconsider the position of developing reports based on the moving target of developing the schema of the datamasrt.

Often, the focus of a project gets a bit blurred for a while - particlarly when critical milestones are approaching. While this is 'understandable', it does not enhance the project to have resources devoted to tasks which simply cannot be done. Report generation is relatively trivial when the source data is known. To attempt to write 'static' reports for an unknown interface (data schema) is frustrating and self-defeating. While I find your attempt to "just deal with it" admirable in spirit, it is primarily a waste of time, as even when you know the changes you STILL need to modify the reports, only to be faced with the same round of uncertainty, investigfation and change thte next hour, day, week ...

Personally, I would re-direct the report generation effort to more productive areas of the project - at least until the database schema was stable.

If it helps you at all, od not hesitate to show this to your boss/supervisor. I am NOT attempting to tell them (anyone) hoe to run their business, just attempting to point out something which may have been overlooked in the stress of managing a large project.


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over