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
