database-integrity-history-reports

database-integrity-history-reports_1.ps1

Import-Module SQLPS 

database-integrity-history-reports_2.ps1

Import-Module SQLPS -DisableNameChecking

database-integrity-history-reports_3.ps1

#replace this with your instance name
$instanceName = "Server\Instance"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$server.ConnectionContext.ConnectTimeout = 2200
$server.ConnectionContext.StatementTimeout = 2200
#store results
$results = @()

database-integrity-history-reports_4.ps1

#for all databases
$databases = $server.Databases

#just a single database
#$databaseName = "dbname"
#$databases = $server.Databases[$databasename]

database-integrity-history-reports_5.ps1

...
$statement = 'CheckCatalog'
# CheckCatalog SQL server 2012
$database.CheckCatalog()

# CheckCatalog pre 2012
#$database.CheckCatalog([Microsoft.SqlServer.Management.Smo.RepairType]::None)

$statement = 'CheckAllocations'
$database.CheckAllocations([Microsoft.SqlServer.Management.Smo.RepairType]::None)

$statement = 'CheckTables'
$database.CheckTables([Microsoft.SqlServer.Management.Smo.RepairType]::None)
...

database-integrity-history-reports_6.ps1

Import-Module SQLPS 
#replace this with your instance name
$instanceName = "Server\Instance"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$server.ConnectionContext.ConnectTimeout = 2200
$server.ConnectionContext.StatementTimeout = 2200
#store results
$results = @()

#for all databases
$databases = $server.Databases

#just a single database
#$databaseName = "dbname"
#$databases = $server.Databases[$databasename]

foreach ($database in $databases) {
    $exception = 'x'
    $statement
    $name = $database.Name

    Try
    {
       $statement = 'CheckCatalog'
       # CheckCatalog SQL server 2012
       $database.CheckCatalog()

       # CheckCatalog pre 2012
       #$database.CheckCatalog([Microsoft.SqlServer.Management.Smo.RepairType]::None)

       $statement = 'CheckAllocations'
       $database.CheckAllocations([Microsoft.SqlServer.Management.Smo.RepairType]::None)

       $statement = 'CheckTables'
       $database.CheckTables([Microsoft.SqlServer.Management.Smo.RepairType]::None)
    }
    Catch 
    { 
        $err = $_.Exception
        while ( $err.InnerException )
        {
            $err = $err.InnerException
        };
        $exception = $err.Message
    }

    $prop = @{
    'Database'= $name    
    'Exception'=$exception
    'Statement'=$statement
    'Date'=Get-Date
    }

   $result = New-Object -TypeName PSObject -Property $prop
   $results = $results + $result 
}

$results | Format-List  | out-file  C:\\Report.txt