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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 |
/** summary: > This query finds the following table smells 1/ is a Wide table (set this to what you consider to be wide) 2/ is a Heap 3/ is an undocumented table 4/ Has no Primary Key 5/ Has ANSI NULLs set to OFF 6/ Has no index at all 7/ No candidate key (unique constraint on column(s)) 8/ Has disabled Index(es) 9/ has leftover fake index(es) 10/ has a column collation different from the database 11/ Has a surprisingly low Fill-Factor 12/ Has disabled constraint(s)' 13/ Has untrusted constraint(s)' 14/ Has a disabled Foreign Key' 15/ Has untrusted FK' 16/ Has unrelated to any other table' 17/ Has a deprecated LOB datatype 18/ Has unintelligible column names' 19/ Has a foreign key that has no index' 20/ Has a GUID in a clustered Index 21/ Has non-compliant column names' 22/ Has a trigger that has'nt got NOCOUNT ON' 23/ Is not referenced by any procedure, view or function' 24/ Has a disabled trigger' 25/ Can't be indexed' Revisions: - Author: Phil Factor Version: 1.1 Modifications: - added tests as suggested by comments to blog Date: 30 Mar 2016 - Author: Phil Factor Version: 1.2 Modifications: - tidying, added five more smells Date: 10 July 2020 returns: > single result of table name, and list of problems **/ WITH TableSmells (TableName, Problem, Object_ID) AS (SELECT Object_Schema_Name(Object_ID) + '.' + Object_Name(Object_ID), Problem, Object_ID FROM ( SELECT object_id, 'wide (more than 15 columns)' FROM sys.tables /* see whether the table has more than 15 columns */ WHERE max_column_id_used > 15 UNION ALL SELECT DISTINCT sys.tables.object_id, 'heap' FROM sys.indexes /* see whether the table is a heap */ INNER JOIN sys.tables ON sys.tables.object_id = sys.indexes.object_id WHERE sys.indexes.type = 0 UNION ALL SELECT s.object_id, 'Undocumented table' FROM sys.objects AS s /* it has no extended properties */ LEFT OUTER JOIN sys.extended_properties AS ep ON s.object_id = ep.major_id AND minor_id = 0 WHERE type_desc = 'USER_TABLE' AND ep.value IS NULL UNION ALL SELECT sys.tables.object_id, 'No primary key' FROM sys.tables /* see whether the table has a primary key */ WHERE ObjectProperty(object_id, 'TableHasPrimaryKey') = 0 UNION ALL SELECT sys.tables.object_id, 'has ANSI NULLs set to OFF' FROM sys.tables /* see whether the table has ansii NULLs off*/ WHERE ObjectPropertyEx(object_id, 'IsAnsiNullsOn') = 0 UNION ALL SELECT sys.tables.object_id, 'No index at all' FROM sys.tables /* see whether the table has any index */ WHERE ObjectProperty(object_id, 'TableHasIndex') = 0 UNION ALL SELECT sys.tables.object_id, 'No candidate key' FROM sys.tables /* if no unique constraint then it isn't relational */ WHERE ObjectProperty(object_id, 'TableHasUniqueCnst') = 0 AND ObjectProperty(object_id, 'TableHasPrimaryKey') = 0 UNION ALL SELECT DISTINCT object_id, 'disabled Index(es)' FROM sys.indexes /* don't leave these lying around */ WHERE is_disabled = 1 UNION ALL SELECT DISTINCT object_id, 'leftover fake index(es)' FROM sys.indexes /* don't leave these lying around */ WHERE is_hypothetical = 1 UNION ALL SELECT c.object_id, 'has a column ''' + c.name + ''' that has a collation ''' + collation_name + ''' different from the database' FROM sys.columns AS c WHERE Coalesce(collation_name, '') <> DatabasePropertyEx(Db_Id(), 'Collation') UNION ALL SELECT DISTINCT object_id, 'surprisingly low Fill-Factor' FROM sys.indexes /* a fill factor of less than 80 raises eyebrows */ WHERE fill_factor <> 0 AND fill_factor < 80 AND is_disabled = 0 AND is_hypothetical = 0 UNION ALL SELECT DISTINCT parent_object_id, 'disabled constraint(s)' FROM sys.check_constraints /* hmm. i wonder why */ WHERE is_disabled = 1 UNION ALL SELECT DISTINCT parent_object_id, 'untrusted constraint(s)' FROM sys.check_constraints /* ETL gone bad? */ WHERE is_not_trusted = 1 UNION ALL SELECT DISTINCT parent_object_id, 'disabled FK' FROM sys.foreign_keys /* build script gone bad? */ WHERE is_disabled = 1 UNION ALL SELECT DISTINCT parent_object_id, 'untrusted FK' FROM sys.foreign_keys /* Why do you have untrusted FKs? Constraint was enabled without checking existing rows; therefore, the constraint may not hold for all rows. */ WHERE is_not_trusted = 1 UNION ALL SELECT object_id, 'unrelated to any other table' FROM sys.tables /* found a simpler way! */ WHERE ObjectPropertyEx(object_id, 'TableHasForeignKey') = 0 AND ObjectPropertyEx(object_id, 'TableHasForeignRef') = 0 UNION ALL SELECT object_id, 'deprecated LOB datatype' FROM sys.tables /* found a simpler way! */ WHERE ObjectPropertyEx(object_id, 'TableHasTextImage') = 1 UNION ALL SELECT DISTINCT object_id, 'unintelligible column names' FROM sys.columns /* column names with no letters in them */ WHERE name COLLATE Latin1_General_CI_AI NOT LIKE '%[A-Z]%' COLLATE Latin1_General_CI_AI UNION ALL SELECT keys.parent_object_id, 'foreign key ' + keys.name + ' that has no supporting index' FROM sys.foreign_keys AS keys INNER JOIN sys.foreign_key_columns AS TheColumns ON keys.object_id = constraint_object_id LEFT OUTER JOIN sys.index_columns AS ic ON ic.object_id = TheColumns.parent_object_id AND ic.column_id = TheColumns.parent_column_id AND TheColumns.constraint_column_id = ic.key_ordinal WHERE ic.object_id IS NULL UNION ALL SELECT Ic.object_id, Col_Name(Ic.object_id, Ic.column_id) + ' is a GUID in a clustered index' /* GUID in a clustered IX */ FROM sys.index_columns AS Ic INNER JOIN sys.tables AS tables ON tables.object_id = Ic.object_id INNER JOIN sys.columns AS c ON c.object_id = Ic.object_id AND c.column_id = Ic.column_id INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id INNER JOIN sys.indexes AS i ON i.object_id = Ic.object_id AND i.index_id = Ic.index_id WHERE t.name = 'uniqueidentifier' AND i.type_desc = 'CLUSTERED' UNION ALL SELECT DISTINCT object_id, 'non-compliant column names' FROM sys.columns /* column names that need delimiters*/ WHERE name COLLATE Latin1_General_CI_AI LIKE '%[^_@$#A-Z0-9]%' COLLATE Latin1_General_CI_AI UNION ALL /* Triggers lacking `SET NOCOUNT ON`, which can cause unexpected results WHEN USING OUTPUT */ SELECT ta.object_id, 'This table''s trigger, ' + Object_Name(tr.object_id) + ', has''nt got NOCOUNT ON' FROM sys.tables AS ta /* see whether the table has any index */ INNER JOIN sys.triggers AS tr ON tr.parent_id = ta.object_id INNER JOIN sys.sql_modules AS mo ON tr.object_id = mo.object_id WHERE definition NOT LIKE '%set nocount on%' UNION ALL /* table not referenced by any routine */ SELECT sys.tables.object_id, 'not referenced by procedure, view or function' FROM sys.tables /* found a simpler way! */ LEFT OUTER JOIN sys.sql_expression_dependencies ON referenced_id = sys.tables.object_id WHERE referenced_id IS NULL UNION ALL SELECT DISTINCT parent_id, 'has a disabled trigger' FROM sys.triggers WHERE is_disabled = 1 AND parent_id > 0 UNION ALL SELECT sys.tables.object_id, 'can''t be indexed' FROM sys.tables /* see whether the table has a primary key */ WHERE ObjectProperty(object_id, 'IsIndexable') = 0 ) AS f(Object_ID, Problem) ) SELECT TableName, CASE WHEN Count(*) > 1 THEN /*only do correlated subquery when necessary*/ Stuff( ( SELECT ', ' + Problem FROM TableSmells AS t2 WHERE t1.TableName = t2.TableName ORDER BY Problem FOR XML PATH(''), TYPE ).value(N'(./text())[1]', N'varchar(8000)'), 1, 2,'' ) ELSE Max(Problem) END AS symptoms FROM TableSmells AS t1 WHERE ObjectPropertyEx(t1.Object_ID, 'IsTable') = 1 AND ObjectPropertyEx(t1.Object_ID, 'IsSystemTable') = 0 GROUP BY TableName; |
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’.
Load comments