The illustrious Phil Factor just released an interesting script over at Simple Talk that looks over your database and analyzes your tables for smells. In this case, table smells have a variety of definitions: a table that is wide (perhaps indicating it needs to be normalized), doesn’t participate in any relationships, doesn’t have a clustered index, etc. A variety of smells exist, it’s an outgrowth of a free ebook published by Red Gate called 119 SQL Code Smells that describe a variety of potential problems.
The book is a good read to drill in some best practices in SQL development by acknowledged luminaries in our field, and the script by Phil, along with some additional scripts in the comments where people would say ‘It would be great if it did X!’ and Phil provided just such a script. The script itself is an interesting study in examining some cool things that you can do by digging in to metadata.