![\"CodeSmells.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/03\/CodeSmells.png\")
<\/p>\nTable 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’.<\/p>\n
<\/p>\n
(from an old AdventureWorks 2008)<\/p>\n
Here is the sort of code \u00a0I 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.<\/p>\n
\/**\r\nsummary: >\r\n This query finds the following table smells\r\n 1\/ is a Wide table (set this to what you consider to be wide)\r\n 2\/ is a Heap\r\n 3\/ is an undocumented table\r\n 4\/ Has no Primary Key\r\n 5\/ Has ANSI NULLs set to OFF\r\n 6\/ Has no index at all\r\n 7\/ No candidate key (unique constraint on column(s))\r\n 8\/ Has disabled Index(es)\r\n 9\/ has leftover fake index(es)\r\n10\/ has a column collation different from the database\r\n11\/ Has a surprisingly low Fill-Factor\r\n12\/ Has disabled constraint(s)'\r\n13\/ Has untrusted constraint(s)'\r\n14\/ Has a disabled Foreign Key'\r\n15\/ Has untrusted FK'\r\n16\/ Has unrelated to any other table'\r\n17\/ Has a deprecated LOB datatype\r\n18\/ Has unintelligible column names'\r\n19\/ Has a foreign key that has no index'\r\n20\/ Has a GUID in a clustered Index\r\n21\/ Has non-compliant column names'\r\n22\/ Has a trigger that has'nt got NOCOUNT ON'\r\n23\/ Is not referenced by any procedure, view or function'\r\n24\/ Has a disabled trigger' \r\n25\/ Can't be indexed'\r\nRevisions:\r\n - Author: Phil Factor\r\n Version: 1.1\r\n Modifications:\r\n\t- added tests as suggested by comments to blog\r\n Date: 30 Mar 2016\r\n - Author: Phil Factor\r\n Version: 1.2\r\n Modifications:\r\n\t- tidying, added five more smells\r\n Date: 10 July 2020\r\n\r\n returns: >\r\n single result of table name, and list of problems \r\n**\/\r\n\r\nWITH TableSmells (TableName, Problem, Object_ID)\r\nAS (SELECT Object_Schema_Name(Object_ID) + '.' + Object_Name(Object_ID),\r\n Problem, Object_ID\r\n FROM\r\n (\r\n SELECT object_id, 'wide (more than 15 columns)'\r\n FROM sys.tables \/* see whether the table has more than 15 columns *\/\r\n WHERE max_column_id_used > 15\r\n UNION ALL\r\n SELECT DISTINCT sys.tables.object_id, 'heap'\r\n FROM sys.indexes \/* see whether the table is a heap *\/\r\n INNER JOIN sys.tables\r\n ON sys.tables.object_id = sys.indexes.object_id\r\n WHERE sys.indexes.type = 0\r\n UNION ALL\r\n SELECT s.object_id, 'Undocumented table'\r\n FROM sys.objects AS s \/* it has no extended properties *\/\r\n LEFT OUTER JOIN sys.extended_properties AS ep\r\n ON s.object_id = ep.major_id AND minor_id = 0\r\n WHERE type_desc = 'USER_TABLE' AND ep.value IS NULL\r\n UNION ALL\r\n SELECT sys.tables.object_id, 'No primary key'\r\n FROM sys.tables \/* see whether the table has a primary key *\/\r\n WHERE ObjectProperty(object_id, 'TableHasPrimaryKey') = 0\r\n UNION ALL\r\n SELECT sys.tables.object_id, 'has ANSI NULLs set to OFF'\r\n FROM sys.tables \/* see whether the table has ansii NULLs off*\/\r\n WHERE ObjectPropertyEx(object_id, 'IsAnsiNullsOn') = 0\r\n UNION ALL\r\n SELECT sys.tables.object_id, 'No index at all'\r\n FROM sys.tables \/* see whether the table has any index *\/\r\n WHERE ObjectProperty(object_id, 'TableHasIndex') = 0\r\n UNION ALL\r\n SELECT sys.tables.object_id, 'No candidate key'\r\n FROM sys.tables \/* if no unique constraint then it isn't relational *\/\r\n WHERE ObjectProperty(object_id, 'TableHasUniqueCnst') = 0\r\n AND ObjectProperty(object_id, 'TableHasPrimaryKey') = 0\r\n UNION ALL\r\n SELECT DISTINCT object_id, 'disabled Index(es)'\r\n FROM sys.indexes \/* don't leave these lying around *\/\r\n WHERE is_disabled = 1\r\n UNION ALL\r\n SELECT DISTINCT object_id, 'leftover fake index(es)'\r\n FROM sys.indexes \/* don't leave these lying around *\/\r\n WHERE is_hypothetical = 1\r\n UNION ALL\r\n SELECT c.object_id,\r\n 'has a column ''' + c.name + ''' that has a collation '''\r\n + collation_name + ''' different from the database'\r\n FROM sys.columns AS c\r\n WHERE Coalesce(collation_name, '') \r\n\t\t <> DatabasePropertyEx(Db_Id(), 'Collation')\r\n UNION ALL\r\n SELECT DISTINCT object_id, 'surprisingly low Fill-Factor'\r\n FROM sys.indexes \/* a fill factor of less than 80 raises eyebrows *\/\r\n WHERE fill_factor <> 0\r\n AND fill_factor < 80\r\n AND is_disabled = 0\r\n AND is_hypothetical = 0\r\n UNION ALL\r\n SELECT DISTINCT parent_object_id, 'disabled constraint(s)'\r\n FROM sys.check_constraints \/* hmm. i wonder why *\/\r\n WHERE is_disabled = 1\r\n UNION ALL\r\n SELECT DISTINCT parent_object_id, 'untrusted constraint(s)'\r\n FROM sys.check_constraints \/* ETL gone bad? *\/\r\n WHERE is_not_trusted = 1\r\n UNION ALL\r\n SELECT DISTINCT parent_object_id, 'disabled FK'\r\n FROM sys.foreign_keys \/* build script gone bad? *\/\r\n WHERE is_disabled = 1\r\n UNION ALL\r\n SELECT DISTINCT parent_object_id, 'untrusted FK'\r\n FROM sys.foreign_keys \/* Why do you have untrusted FKs? \r\n Constraint was enabled without checking existing rows;\r\n therefore, the constraint may not hold for all rows. *\/\r\n WHERE is_not_trusted = 1\r\n UNION ALL\r\n SELECT object_id, 'unrelated to any other table'\r\n FROM sys.tables \/* found a simpler way! *\/\r\n WHERE ObjectPropertyEx(object_id, 'TableHasForeignKey') = 0\r\n AND ObjectPropertyEx(object_id, 'TableHasForeignRef') = 0\r\n UNION ALL\r\n SELECT object_id, 'deprecated LOB datatype'\r\n FROM sys.tables \/* found a simpler way! *\/\r\n WHERE ObjectPropertyEx(object_id, 'TableHasTextImage') = 1 \r\n UNION ALL\r\n SELECT DISTINCT object_id, 'unintelligible column names'\r\n FROM sys.columns \/* column names with no letters in them *\/\r\n WHERE name COLLATE Latin1_General_CI_AI NOT LIKE '%[A-Z]%' COLLATE Latin1_General_CI_AI\r\n UNION ALL\r\n SELECT keys.parent_object_id,\r\n 'foreign key ' + keys.name + ' that has no supporting index'\r\n FROM sys.foreign_keys AS keys\r\n INNER JOIN sys.foreign_key_columns AS TheColumns\r\n ON keys.object_id = constraint_object_id\r\n LEFT OUTER JOIN sys.index_columns AS ic\r\n ON ic.object_id = TheColumns.parent_object_id\r\n AND ic.column_id = TheColumns.parent_column_id\r\n AND TheColumns.constraint_column_id = ic.key_ordinal\r\n WHERE ic.object_id IS NULL\r\n UNION ALL\r\n SELECT Ic.object_id, Col_Name(Ic.object_id, Ic.column_id)\r\n + ' is a GUID in a clustered index' \/* GUID in a clustered IX *\/\r\n FROM sys.index_columns AS Ic\r\n\t\t\tINNER JOIN sys.tables AS tables\r\n\t\t\tON tables.object_id = Ic.object_id\r\n INNER JOIN sys.columns AS c\r\n ON c.object_id = Ic.object_id AND c.column_id = Ic.column_id\r\n INNER JOIN sys.types AS t\r\n ON t.system_type_id = c.system_type_id\r\n INNER JOIN sys.indexes AS i\r\n ON i.object_id = Ic.object_id AND i.index_id = Ic.index_id\r\n WHERE t.name = 'uniqueidentifier'\r\n AND i.type_desc = 'CLUSTERED'\r\n UNION ALL\r\n SELECT DISTINCT object_id, 'non-compliant column names'\r\n FROM sys.columns \/* column names that need delimiters*\/\r\n WHERE name COLLATE Latin1_General_CI_AI LIKE '%[^_@$#A-Z0-9]%' COLLATE Latin1_General_CI_AI\r\n UNION ALL \/* Triggers lacking `SET NOCOUNT ON`, which can cause unexpected results WHEN USING OUTPUT *\/\r\n SELECT ta.object_id,\r\n 'This table''s trigger, ' + Object_Name(tr.object_id)\r\n + ', has''nt got NOCOUNT ON'\r\n FROM sys.tables AS ta \/* see whether the table has any index *\/\r\n INNER JOIN sys.triggers AS tr\r\n ON tr.parent_id = ta.object_id\r\n INNER JOIN sys.sql_modules AS mo\r\n ON tr.object_id = mo.object_id\r\n WHERE definition NOT LIKE '%set nocount on%'\r\n UNION ALL \/* table not referenced by any routine *\/\r\n SELECT sys.tables.object_id,\r\n 'not referenced by procedure, view or function'\r\n FROM sys.tables \/* found a simpler way! *\/\r\n LEFT OUTER JOIN sys.sql_expression_dependencies\r\n ON referenced_id = sys.tables.object_id\r\n WHERE referenced_id IS NULL\r\n UNION ALL\r\n SELECT DISTINCT parent_id, 'has a disabled trigger'\r\n FROM sys.triggers\r\n WHERE is_disabled = 1 AND parent_id > 0\r\n UNION ALL\r\n SELECT sys.tables.object_id, 'can''t be indexed'\r\n FROM sys.tables \/* see whether the table has a primary key *\/\r\n WHERE ObjectProperty(object_id, 'IsIndexable') = 0\r\n ) AS f(Object_ID, Problem) )\r\n SELECT TableName,\r\n CASE WHEN Count(*) > 1 THEN \/*only do correlated subquery when necessary*\/\r\n Stuff(\r\n (\r\n SELECT ', ' + Problem\r\n FROM TableSmells AS t2\r\n WHERE t1.TableName = t2.TableName\r\n ORDER BY Problem\r\n FOR XML PATH(''), TYPE\r\n ).value(N'(.\/text())[1]', N'varchar(8000)'), 1, 2,'' ) \r\n\t ELSE Max(Problem) \r\n\tEND AS symptoms\r\n FROM TableSmells AS t1\r\n WHERE ObjectPropertyEx(t1.Object_ID, 'IsTable') = 1\r\n AND ObjectPropertyEx(t1.Object_ID, 'IsSystemTable') = 0\r\n GROUP BY TableName;<\/pre>\nSo there is the code. What other types of table smell do you look for, and how do you search for it?<\/p>\n
Revised: 31st March 2016<\/i>: Added ‘smells’ requested by readers of the blog<\/p>\n
Revised: 13th July 2020<\/i>: Added more ‘smells’.<\/p>\n","protected":false},"excerpt":{"rendered":"
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,……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6813],"class_list":["post-8800","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8800","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=8800"}],"version-history":[{"count":13,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8800\/revisions"}],"predecessor-version":[{"id":87575,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8800\/revisions\/87575"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=8800"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=8800"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=8800"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=8800"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}