ÿþSET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck]



@Databases nvarchar(max),

@PhysicalOnly nvarchar(max) = 'N',

@NoIndex nvarchar(max) = 'N',

@ExtendedLogicalChecks nvarchar(max) = 'N',

@TabLock nvarchar(max) = 'N',

@LogToTable nvarchar(max) = 'N',

@Execute nvarchar(max) = 'Y'



AS



BEGIN



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

  --// Source: http://ola.hallengren.com                                                          //--

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



  SET NOCOUNT ON



  DECLARE @StartMessage nvarchar(max)

  DECLARE @EndMessage nvarchar(max)

  DECLARE @DatabaseMessage nvarchar(max)

  DECLARE @ErrorMessage nvarchar(max)



  DECLARE @Version numeric(18,10)



  DECLARE @CurrentID int

  DECLARE @CurrentDatabaseID int

  DECLARE @CurrentDatabaseName nvarchar(max)

  DECLARE @CurrentIsDatabaseAccessible bit

  DECLARE @CurrentMirroringRole nvarchar(max)



  DECLARE @CurrentCommand01 nvarchar(max)



  DECLARE @CurrentCommandOutput01 int



  DECLARE @CurrentCommandType01 nvarchar(max)



  DECLARE @tmpDatabases TABLE (ID int IDENTITY PRIMARY KEY,

                               DatabaseName nvarchar(max),

                               Completed bit)



  DECLARE @Error int

  DECLARE @ReturnCode int



  SET @Error = 0

  SET @ReturnCode = 0



  SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))



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

  --// Log initial information                                                                    //--

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



  SET @StartMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @PhysicalOnly = ' + ISNULL('''' + REPLACE(@PhysicalOnly,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @NoIndex = ' + ISNULL('''' + REPLACE(@NoIndex,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @ExtendedLogicalChecks = ' + ISNULL('''' + REPLACE(@ExtendedLogicalChecks,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @TabLock = ' + ISNULL('''' + REPLACE(@TabLock,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Source: http://ola.hallengren.com' + CHAR(13) + CHAR(10) + ' '

  SET @StartMessage = REPLACE(@StartMessage,'%','%%')

  RAISERROR(@StartMessage,10,1) WITH NOWAIT



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

  --// Check core requirements                                                                    //--

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



  IF SERVERPROPERTY('EngineEdition') = 5

  BEGIN

    SET @ErrorMessage = 'SQL Azure is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @Error <> 0

  BEGIN

    SET @ReturnCode = @Error

    GOTO Logging

  END



  IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute')

  BEGIN

    SET @ErrorMessage = 'The stored procedure CommandExecute is missing. Download http://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute' AND OBJECT_DEFINITION(objects.[object_id]) NOT LIKE '%@LogToTable%')

  BEGIN

    SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download http://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'TF' AND schemas.[name] = 'dbo' AND objects.[name] = 'DatabaseSelect')

  BEGIN

    SET @ErrorMessage = 'The function DatabaseSelect is missing. Download http://ola.hallengren.com/scripts/DatabaseSelect.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')

  BEGIN

    SET @ErrorMessage = 'The table CommandLog is missing. Download http://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @Error <> 0

  BEGIN

    SET @ReturnCode = @Error

    GOTO Logging

  END



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

  --// Select databases                                                                           //--

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



  IF @Databases IS NULL OR @Databases = ''

  BEGIN

    SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  INSERT INTO @tmpDatabases (DatabaseName, Completed)

  SELECT DatabaseName AS DatabaseName,

         0 AS Completed

  FROM dbo.DatabaseSelect (@Databases)

  ORDER BY DatabaseName ASC



  IF @@ERROR <> 0

  BEGIN

    SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



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

  --// Check input parameters                                                                     //--

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



  IF @PhysicalOnly NOT IN ('Y','N') OR @PhysicalOnly IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @PhysicalOnly is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @NoIndex NOT IN ('Y','N') OR @NoIndex IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @NoIndex is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @ExtendedLogicalChecks NOT IN ('Y','N') OR @ExtendedLogicalChecks IS NULL OR (@ExtendedLogicalChecks = 'Y' AND NOT @Version >= 10)

  BEGIN

    SET @ErrorMessage = 'The value for parameter @ExtendedLogicalChecks is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @PhysicalOnly = 'Y' AND @ExtendedLogicalChecks = 'Y'

  BEGIN

    SET @ErrorMessage = 'Extended Logical Checks and Physical Only cannot be used together.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF (@ExtendedLogicalChecks = 'Y' AND NOT (@Version >= 10))

  BEGIN

    SET @ErrorMessage = 'Extended Logical Checks are only supported in SQL Server 2008 and SQL Server 2008 R2.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @TabLock NOT IN ('Y','N') OR @TabLock IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @TabLock is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @Execute NOT IN('Y','N') OR @Execute IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @Error <> 0

  BEGIN

    SET @ErrorMessage = 'The documentation is available on http://ola.hallengren.com/Documentation.html.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @ReturnCode = @Error

    GOTO Logging

  END



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

  --// Execute commands                                                                           //--

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



  WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)

  BEGIN



    SELECT TOP 1 @CurrentID = ID,

                 @CurrentDatabaseName = DatabaseName

    FROM @tmpDatabases

    WHERE Completed = 0

    ORDER BY ID ASC



    SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)



    IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)

    BEGIN

      SET @CurrentIsDatabaseAccessible = 1

    END

    ELSE

    BEGIN

      SET @CurrentIsDatabaseAccessible = 0

    END



    SELECT @CurrentMirroringRole = mirroring_role_desc

    FROM sys.database_mirroring

    WHERE database_id = @CurrentDatabaseID



    -- Set database message

    SET @DatabaseMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabaseName) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') AS nvarchar) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Mirroring role: ' + ISNULL(@CurrentMirroringRole,'N/A') + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') AS nvarchar) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10) + ' '

    SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%')

    RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT



    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'

    AND NOT (DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)

    BEGIN

      SET @CurrentCommandType01 = 'DBCC_CHECKDB'



      SET @CurrentCommand01 = 'DBCC CHECKDB (' + QUOTENAME(@CurrentDatabaseName)

      IF @NoIndex = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', NOINDEX'

      SET @CurrentCommand01 = @CurrentCommand01 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'

      IF @PhysicalOnly = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ', DATA_PURITY'

      IF @PhysicalOnly = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', PHYSICAL_ONLY'

      IF @ExtendedLogicalChecks = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', EXTENDED_LOGICAL_CHECKS'

      IF @TabLock = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', TABLOCK'



      EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @Command = @CurrentCommand01, @CommandType = @CurrentCommandType01, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute

      SET @Error = @@ERROR

      IF @Error <> 0 SET @CurrentCommandOutput01 = @Error

      IF @CurrentCommandOutput01 <> 0 SET @ReturnCode = @CurrentCommandOutput01

    END



    -- Update that the database is completed

    UPDATE @tmpDatabases

    SET Completed = 1

    WHERE ID = @CurrentID



    -- Clear variables

    SET @CurrentID = NULL

    SET @CurrentDatabaseID = NULL

    SET @CurrentDatabaseName = NULL

    SET @CurrentIsDatabaseAccessible = NULL

    SET @CurrentMirroringRole = NULL



    SET @CurrentCommand01 = NULL



    SET @CurrentCommandOutput01 = NULL



    SET @CurrentCommandType01 = NULL



  END



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

  --// Log completing information                                                                 //--

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



  Logging:

  SET @EndMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120)

  SET @EndMessage = REPLACE(@EndMessage,'%','%%')

  RAISERROR(@EndMessage,10,1) WITH NOWAIT



  IF @ReturnCode <> 0

  BEGIN

    RETURN @ReturnCode

  END



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



END

GO