Finding redundant columns
The task is to refactor a large SQL Server database design with over 1100 tables, 3500 stored procedures, 600 views and 500 functions. One of the areas of focus is reducing the size of the tables, by getting rid of columns that have been created but never used. A lot of columns had been added for the future or "just in case", but this has caused unnecessary bloat. By using the INFORMATION_SCHEMA.columns we have been able to find columns that are all just NULL values. The next step is to then find columns that only have one distinct value in them and then review these to see if they are required. Thinner tables makes the database design easier to handle and will help better performance, especially as some of these redundant columns were indexed as well. This needs co-ordination with the application developers to make sure that these columns are not used in any applications and then once the columns identified, drop them in a non-production environment and do end to end testing.