We have a collection of very old, horribly designed databases and I have been given the green light to tear them out and restructure them. However, these databases have no normalization, numerous fields have been empty for years, or re-purposed without documentation, and we have a slew of legacy applications and public websites that use various pieces of this data, but no one in house has any idea what does what to what.
How could I determine which fields in these tables are being used? Is there a way in SQL server, or using a third party tool if need be, to see a history of usage? or to set up some additional logging to determine this usage? "Usage" ideally meaning when it was last updated or inserted, and when or how often it is included in a select statement.
In addition to this, we are migrating from SQL 2005 to 2008. So a solution using either server type would work as I should be able to bring the 05 dbs up to 08 compliance.