Working around a bad vendor

[William Cleveland]

A lot of our data is in a database originally provided to us by
a vendor. We can add tables to the database (although we prefix
them with our own company name), and stored procedures and our
own triggers, as well. However, we have been hesitant to
change the vendor's tables (despite serious flaws), as our
changes may get lost whenever we put in a new version of their
software.

I would like to be able to run a script that will check the
database for each of our changes, and put the change back in
if it was missing. These are the main things I'd like to
check:

Foreign keys are present.
Check constraints are present.
Specific triggers are absent (The vendor has a bad habit
of implementing foreign keys and simple checks in
triggers. The foreign key checks cause us a lot of
locking trouble). If present, and not the same as the
one that was originally removed, report it instead of
removing it.
Calculated fields are present.
Indexes are present. (especially if we can check what is
indexed, and what index is clustered)
Any tips? Books to read? Software to buy?

Thanks,
Bill

[Ray Higdon]

Unless they specifically call indexes (rare in my experience from
vendors), you should be able to add, delete and modify indexes. I bet
poor indexing is what is causing you locking problems.

The placement of clustered indexes is real important and too often I've
seen vendors put them on the most queried column (causing lots of page
splits when doing inserts, updates), use composite clustered indexes
(then have a non-clustered index on almost every table, making NCI's
large and updates, inserts painfully slow) or have tables with no
clustered index.(causing heaps to search for empty space upon inserts
and not utlizing efficient key locks)

If you check with the vendor they have probably told you that you cannot
change anything, right??

The real issue is if your company is going to stay with that vendor (and
your company doesn't plan on divorcing them) you need to have management
support you in asking for things to be fixed. If management doesn't
support you, I don't think I would take the time to make their
software/database better, thereby increasing the chance they stick with
that vendor. Obviously a fine line there...

As far as your actual question, I haven't used it but there is a
software out there called SQL compare that compares structures.

http://www.red-gate.com/SQL_Compare.htm

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!