I am facing the task of refactoring a database whose structure has accreted through the years. Table names like "Foo" and "FooX." Something like 20 tables that have no records (those are easy to scratch), others that have virtually the same columns and yet which each have thousands of records, indicating they're both in use.
I don't generally deal with database stuff at this level. I thought I'd use Visio to help me re-engineer the database, but it seems that it only reverse-engineers, it doesn't forward-engineer or even generate DDL.
I grabbed Scott Amble's "Agile Database Techniques" off the shelf, but it didn't seem very concrete in terms of the tasks I'm actually facing, like "How do I generate a list of all columns that are null, or only have 1 value?" and "Is there a way to tell the date on which the table data was last modified?" I know that the answers to these sorts of questions lie in the system tables, but it's hard to find a concise guide to their structure and use.
Scott emphasizes unit-tests to maintain data integrity. OK, check: require 'test/unit'
Other than that, I've thought about installing the Visual Studio CTP for Database Professionals, but I'm not sure if this is what it does or if it is geared for other tasks.