SQL Server Table Smells

Comments 0

Share to social media

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

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

About the author

Phil Factor

See Profile

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 40 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Phil Factor's contributions