SQL Server Table Smells

Table smells in SQL Server are just like code smells. They are just an indication that something may need to be checked or investigated. They are easy to check as well, because you can generally ferret through the object catalog metadata views to flush out the aspects that just don’t smell right. In this blog, I show the sort of query I’ll use. Actually, I generally use rather more strict criteria because I’d be concerned about tables that don’t seem to be making full use of constraints, and tables that don’t have ‘soft’ dependencies (aren’t referenced in code within the metadata, from views, procedures or functions). I like to have a result that displays the names of the tables that look suspect, along with the list of the ‘smells’.

CodeSmells.png

(from an old AdventureWorks 2008)

Here is the sort of code  I use. Obviously, if there are ‘smells’ that you don’t consider to be worth investigating, then just comment them out of the version that you use.

So there is the code. What other types of table smell do you look for, and how do you search for it?

Revised: 31st March 2016: Added ‘smells’ requested by readers of the blog

  • 10234 views

  • Rate
    [Total: 2    Average: 5/5]
  • Unknown Stranger

    Does it not report “FK xxxxxx has no index” when the referenced table is indeed indexed by its PK? Isn’t the PK an index ? I must be missing something?!

  • DrowningInSPs

    I wonder about tables missing an unique index. Usually this is not a truly problem, but on some it points to not thinking about its usage. (I wish MS SQL Server actually had a true Table Type and not just requiring people to use custom proprietary Extended Properties. This would help code generators and help with the code smell detectors. Examples of table types would be “Master Data” tables (such as Customer and Addresses), “System Code” tables (for application dropdowns), “Bridge” tables (that have FKs to 2 different tables so one could do many-to-many), “Event Header” tables, “Event Detail” tables, etc.)