SQL Server Table Smells – Simple-Talk

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;
 

Stored Procedure script to Enable trace flags on SQLServer start.

USE [master]
GO
 
CREATE PROC [dbo].[EnableTraceFlags]
AS
DBCC TRACEON (1222, -1); -- Write deadlocks to error log
DBCC TRACEON (3226, -1); -- Suppress successful backup messages

GO

EXEC sp_procoption N'[dbo].[EnableTraceFlags]', 'startup', '1'

GO

Stored Procedure script to show all jobs

USE [master]
GO

create proc [dbo].[sp_ShowJobs]
as
SELECT
j.name AS 'Job', *
FROM msdb.dbo.sysschedules sched
JOIN msdb.dbo.sysjobschedules jsched ON sched.schedule_id = jsched.schedule_id
JOIN msdb.dbo.sysjobs j ON jsched.job_id = j.job_id

GO

Stored Procedure script to show startup Procs

USE [master]
GO

CREATE proc [dbo].[sp_ShowStartupProcs]
as
SELECT *
FROM [sysobjects]
WHERE [type] = 'P' AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1;

GO