Friday 30 November 2012

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

Thursday 29 November 2012

Tables without a Clustered Index


Yesterday while adding some new logic to an existing process I noticed an unacceptable level of performance degradation with the routine.  Investigation led me to find that a new table had slipped through the net and been created without a clustered index.  Needless to say adding a clustered index and rebuilding the non-clustered indexes on the table improved the performance of the routine significantly.

Being a DBA, this got me thinking about how this had slipped through the net, how I could prevent it in future and equally as important how many other existing tables didn't have a Clustered Index?  I knocked together the below script which will create a SQL Job that is scheduled to run every Monday at 07:07 and email an operator if there are any tables without a clustered index in all user databases.

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.

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 clustered index
      -----------------------------------------------------------------
    
      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 - TablesWithoutClusteredIndex')
EXEC msdb.dbo.sp_delete_job @job_name = N'Alert - TablesWithoutClusteredIndex', @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 - TablesWithoutClusteredIndex',
            @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 on a Monday morning at 07:07 and will email if there are any tables without a clustered index 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 Clustered Index]    Script Date: 11/29/2012 12:20:42 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Find tables Without a Clustered Index',
            @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.#NoClusteredIndex'') IS NOT NULL
    DROP TABLE #NoClusteredIndex;
     
-- Create Temporary Table
CREATE TABLE #NoClusteredIndex
    (
      DatabaseName VARCHAR(255) ,
      SchemaName VARCHAR(255) ,
      TableName VARCHAR(1000)
    );

INSERT INTO #NoClusteredIndex
EXEC sp_msforeachdb ''USE [?];
IF ''''?'''' NOT IN (''''master'''', ''''model'''',''''msdb'''', ''''tempdb'''', ''''ReportServer'''', ''''ReportServerTempDB'''', ''''Distribution'''')
BEGIN
SELECT DISTINCT  DB_NAME() AS DatabaseName, SCHEMA_NAME(s.[schema_id]) AS SchemaName, OBJECT_NAME(i.[object_id]) AS TableName
FROM sys.indexes i
INNER JOIN sys.objects o ON o.[object_id] = i.[object_id]
INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id]
WHERE i.INDEX_ID = 0
AND OBJECTPROPERTY(i.[object_id],''''IsUserTable'''') = 1
ORDER BY DatabaseName, SchemaName, TableName ASC;
END
''

-- Check for tables without a clustered index
IF EXISTS ( SELECT  1
            FROM #NoClusteredIndex)
    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   #NoClusteredIndex
                   FOR
                     XML PATH(''tr'') ,
                         TYPE
                   ) AS NVARCHAR(MAX)) + N''</table>''; 
     
            -- Count tables
            SELECT @TableCount = COUNT(1) FROM #NoClusteredIndex;
           
            -- Count tables
            SELECT @DatabaseCount = COUNT(DISTINCT DatabaseName) FROM #NoClusteredIndex;
     
            -- Set subject
            SET @EmailSubject = ''ALERT - '' + CAST(@TableCount AS VARCHAR(100)) +  '' Tables without a Clustered Index 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:07) 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=70700,
            @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