Checks/Schemas.Tests.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
param (
    [object]$config,
    [Object]$SqlInstance,
    [PSCredential]$SqlCredential,
    [String]$Database
)

# FIXME: Potential dbatools command to migrate across. Get Schema details
$sqlSchema = "
        select
            ss.name as 'schemaName',
            sdp.name as 'owner'
        from
            sys.schemas ss inner join sys.database_principals sdp
                on ss.principal_id=sdp.principal_id
"

$dbSchema = Invoke-DbaQuery -SqlInstance $SqlInstance -sqlcredential $sqlcredential -database $database -Query $sqlSchema 

Foreach ($schema in $config.schemas) {
    Describe "Checking schema $($schema.schemaname)" {
        It "Schema $($schema.schemaname) should exist and be owned by $($schema.owner)" {
            ($dbSchema | Where-Object {$_.schemaName -eq $schema.schemaname -and $_.owner -eq $schema.owner} | Measure-Object).count | Should -Be 1
        }
        $checkSql = "select name, type_desc from sys.all_objects where schema_id=SCHEMA_ID('$($schema.schemaname)') and is_ms_shipped=0"
        $objectsSchema = Invoke-DbaQuery -SqlInstance $SqlInstance -sqlcredential $sqlcredential -database $database -Query $checkSql 
        
        # $($schema.objects)
        $title = ($schema.objects) -join ','
        It "Schema $($schema.schemaname) should contain $title Objects" {
            $objectsSchema.count | Should -Be $schema.objects.count -Because "The schema should only contain the specified objects"
        }

        Foreach ($object in  $schema.objects) {
            It "$($schema.schemaname) should contain $($object.object)"{
                ($objectsSchema | Where-Object {$_.name -eq $object.Object } | Measure-Object).count | Should -Be 1
            }
        }
    }
}