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’.


(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

Revised: 13th July 2020: Added more ‘smells’.