Here is the corrected version of
Phil's table smell:
Note: This needs to be run
per database.
create proc dbo.sp_CodeSmells
as
;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 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, '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 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 sys.tables.object_id, 'unrelated to any other table'
FROM sys.tables
LEFT OUTER join
(SELECT referenced_object_id AS table_ID
FROM sys.foreign_keys
UNION ALL
SELECT parent_object_id
FROM sys.foreign_keys
)referenced(table_ID)
ON referenced.table_ID=sys.Tables.object_ID
WHERE referenced.table_id IS null*/
SELECT sys.tables.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 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 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
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
)f(Object_ID,Problem)
)
SELECT TableName,
CASE WHEN count(*)>1 THEN /*only do correlated subquery when necessary*/
stuff(( SELECT ', '+Problem
FROM TableSmells 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
FROM TableSmells t1 WHERE OBJECTPROPERTYEX(t1.Object_ID, 'IsTable')=1
GROUP BY TableName;
