Tables without a Primary Key

As well as creating the script for yesterday’s post on finding Tables without a Clustered Index I tweaked the script slightly to get all tables without a primary key.  A Primary Key constraint enforces uniqueness of a record in a table.  I am extremely anal with this particular standard that I try to enforce, to say I don’t take kindly to folks that don’t create a Primary Key on their tables is an understatement.

There are a few things that will need changing;

·         @notify_email_operator_name=N'Chris'

·         SET @EmailProfile = ''Chris''

·         SET @EmailRecipient = ''Chris@SQLServer365.co.uk''

You can also obviously change the schedule accordingly to suit your needs.

Again I would be interested to know the results on your servers, leave me a comment with the email subject text from the resulting email.

/*

      -----------------------------------------------------------------

      Find tables without a Primary Key

      -----------------------------------------------------------------

    

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set database context

USE [msdb]

GO

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Alert - TablesWithoutPrimaryKey')

EXEC msdb.dbo.sp_delete_job @job_name = N'Alert - TablesWithoutPrimaryKey', @delete_unused_schedule=1

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Alert - TablesWithoutPrimaryKey',

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=2,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'This job runs every Monday at 07:09 and will email an operator if there are any tables without a Primary key in any user database.',

            @category_name=N'[Uncategorized (Local)]',

            @owner_login_name=N'sa',

            @notify_email_operator_name=N'Chris', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Find tables without a Primary Key]    Script Date: 11/29/2012 13:31:29 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Find tables without a Primary Key',

            @step_id=1,

            @cmdexec_success_code=0,

            @on_success_action=1,

            @on_success_step_id=0,

            @on_fail_action=2,

            @on_fail_step_id=0,

            @retry_attempts=0,

            @retry_interval=0,

            @os_run_priority=0, @subsystem=N'TSQL',

            @command=N'-- Set database context

USE master;

GO

-- Declare variables

DECLARE @EmailProfile VARCHAR(255)

DECLARE @EmailRecipient VARCHAR(255)

DECLARE @EmailSubject VARCHAR(255)

DECLARE @IndexUsageStats INT

DECLARE @TableCount INT

DECLARE @DatabaseCount INT

-- Set variables

SET @EmailProfile = ''Chris''

SET @EmailRecipient = ''Chris.McGowan@SQLServer365.co.uk''

-- Drop temporary table if exists

IF OBJECT_ID(''tempDB.dbo.#NoPrimaryKey'') IS NOT NULL

    DROP TABLE #NoPrimaryKey;

     

-- Create Temporary Table

CREATE TABLE #NoPrimaryKey

    (

      DatabaseName VARCHAR(255) ,

      SchemaName VARCHAR(255) ,

      TableName VARCHAR(1000)

    );

INSERT INTO #NoPrimaryKey

EXEC sp_msforeachdb ''USE [?];

IF ''''?'''' NOT IN (''''master'''', ''''model'''',''''msdb'''', ''''tempdb'''', ''''ReportServer'''', ''''ReportServerTempDB'''', ''''Distribution'''')

BEGIN

SELECT DB_NAME() AS DatabaseName, SCHEMA_NAME(s.[schema_id]) AS SchemaName, OBJECT_NAME(t.[object_id]) AS TableName

FROM sys.tables t

INNER JOIN sys.objects o ON o.[object_id] = t.[object_id]

INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id]

WHERE OBJECTPROPERTY(t.[OBJECT_ID],''''TableHasPrimaryKey'''') = 0

AND OBJECTPROPERTY(t.[object_id],''''IsUserTable'''') = 1

AND o.is_ms_shipped = 0

ORDER BY DatabaseName, SchemaName, TableName ASC;

END

''

-- Check for tables without a Primary key

IF EXISTS ( SELECT  1

            FROM #NoPrimaryKey)

    BEGIN

        DECLARE @tableHTML NVARCHAR(MAX); 

        SET @tableHTML = N''<style type="text/css">''

            + N''.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} ''

            + N''.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} ''

            + N''body {font-family: Arial, verdana;} ''

            + N''table{font-size:12px; border-collapse:collapse;border:1px solid black; padding:3px;} ''

            + N''td{background-color:#F1F1F1; border:1px solid black; padding:3px;} ''

            + N''th{background-color:#99CCFF; border:1px solid black; padding:3px;}''

            + N''</style>'' + N''<table border="1">'' + N''<tr>''

            + N''<th>DatabaseName</th>''

            + N''<th>SchemaName</th>''

            + N''<th>TableName</th>''

            + N''</tr>''

            + CAST(( SELECT td = DatabaseName,

                            '''',

                            td = SchemaName,

                            '''',

                            td = TableName,

                            ''''                        

                     FROM   #NoPrimaryKey

                   FOR

                     XML PATH(''tr'') ,

                         TYPE

                   ) AS NVARCHAR(MAX)) + N''</table>''; 

     

            -- Count tables

            SELECT @TableCount = COUNT(1) FROM #NoPrimaryKey;

           

            -- Count tables

            SELECT @DatabaseCount = COUNT(DISTINCT DatabaseName) FROM #NoPrimaryKey;

     

            -- Set subject

            SET @EmailSubject = ''ALERT - '' + CAST(@TableCount AS VARCHAR(100)) +  '' Tables without a Primary Key on '' + @@SERVERNAME + '' accross '' + CAST(@DatabaseCount AS VARCHAR(100)) + '' databases''

           

            -- Email results 

        EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,

            @recipients = @EmailRecipient, @subject = @EmailSubject,

            @body = @tableHTML, @body_format = ''HTML''; 

    END

    GO

',

            @database_name=N'master',

            @flags=12

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'(07:09) Monday',

            @enabled=1,

            @freq_type=8,

            @freq_interval=2,

            @freq_subday_type=1,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=1,

            @active_start_date=20121129,

            @active_end_date=99991231,

            @active_start_time=70900,

            @active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Enjoy!

Chris