Generating TSQLT - from SQLStad

Generate - Does SP exist tests

# Import the dbatools module
Import-Module dbatools

# Set the global values
$instance = 'localhost,15789'
$db = 'WideWorldImporters'
$date = (Get-Date).ToString('yyyy-MM-dd')
$creatorName = 'Beardy McBeardFace'
$cred = Import-Clixml -Path D:\Creds\containersa.xml
$TestClass = 'TestExistence'

# Get the database object 
$database = Get-DbaDatabase -SqlInstance $instance -Database $db -SqlCredential $cred

#region Create TestClass
$query = @"
IF NOT EXISTS 
(
SELECT name FROM sys.schemas WHERE name = '$TestClass'
)

EXEC('CREATE SCHEMA [$TestClass]')

IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = SCHEMA_ID('$TestClass') AND [name] = N'tSQLt.TestClass' AND [minor_id] = 0)
EXEC sys.sp_addextendedproperty @name=N'tSQLt.TestClass', @value=1 , @level0type=N'SCHEMA',@level0name=N'$TestClass'
"@

    # Execute the query
    Write-Host "Creating testclass $TestClass"
    try{
       # $query
        Invoke-DbaSqlQuery -SqlInstance $instance -Database $db -Query $query -SqlCredential $cred
    }
    catch{
        Write-Error "Something went wrong Creating testclass $TestClass`n$_"
    }
#endregion

#region Stored procedures
# Only select the procedures that are not a system object or TSQLt sps
$procedures = $database.StoredProcedures | Where-Object {$_.IsSystemObject -eq $false -and $_.Schema -ne 'tSQLt'} | Select-Object Name, Schema

# Loop through the objects
foreach($procedure in $procedures){
    # Setup the query
    $query = "
/*
Description:
Test if the stored procedure $($procedure.Schema).$($procedure.Name) exists

Changes:
Date		Who						Notes
----------	---						--------------------------------------------------------------
$date	$creatorName				Initial procedure
*/
CREATE PROCEDURE [$Testclass].[test If stored procedure $($procedure.Schema).$($procedure.Name) exists]
AS
BEGIN
    SET NOCOUNT ON;

    ----- ASSERT -------------------------------------------------
    EXEC tSQLt.AssertObjectExists @ObjectName = N'$($procedure.Schema).$($procedure.Name)';

END;
    "
    
    # Execute the query
    Write-Host "Creating test for $($procedure.Schema).$($procedure.Name)"
    try{
       # $query
        Invoke-DbaSqlQuery -SqlInstance $instance -Database $db -Query $query -SqlCredential $cred
    }
    catch{
        Write-Error "Something went wrong creating the test for $($procedure.Schema).$($procedure.Name)`n$_"
    }
}
#endregion

#region tables
# Only select the procedures that are not a system object or TSQLt sps
$tables = $database.tables | Where-Object {$_.IsSystemObject -eq $false -and $_.Schema -ne 'tSQLt'} | Select-Object Name, Schema

# Loop through the objects
foreach($table in $tables){
    # Setup the query
    $query = "
/*
Description:
Test if the table $($table.Schema).$($table.Name) exists

Changes:
Date		Who						Notes
----------	---						--------------------------------------------------------------
$date	$creatorName				Initial procedure
*/
CREATE PROCEDURE [$Testclass].[test If table $($table.Schema).$($table.Name) exists]
AS
BEGIN
    SET NOCOUNT ON;

    ----- ASSERT -------------------------------------------------
    EXEC tSQLt.AssertObjectExists @ObjectName = N'$($table.Schema).$($table.Name)';

END;
    "
    
    # Execute the query
    Write-Host "Creating test for $($table.Schema).$($table.Name)"
    try{
       # $query
        Invoke-DbaSqlQuery -SqlInstance $instance -Database $db -Query $query -SqlCredential $cred
    }
    catch{
        Write-Error "Something went wrong creating the test for $($table.Schema).$($table.Name)`n$_"
    }
}
#endregion

#region views
# Only select the procedures that are not a system object or TSQLt sps
$views = $database.Views | Where-Object {$_.IsSystemObject -eq $false -and $_.Schema -ne 'tSQLt'} | Select-Object Name, Schema

# Loop through the objects
foreach($view in $views){
    # Setup the query
    $query = "
/*
Description:
Test if the view $($view.Schema).$($view.Name) exists

Changes:
Date		Who						Notes
----------	---						--------------------------------------------------------------
$date	$creatorName				Initial procedure
*/
CREATE PROCEDURE [$Testclass].[test If view $($view.Schema).$($view.Name) exists]
AS
BEGIN
    SET NOCOUNT ON;

    ----- ASSERT -------------------------------------------------
    EXEC tSQLt.AssertObjectExists @ObjectName = N'$($view.Schema).$($view.Name)';

END;
    "
    
    # Execute the query
    Write-Host "Creating test for $($view.Schema).$($view.Name)"
    try{
       # $query
        Invoke-DbaSqlQuery -SqlInstance $instance -Database $db -Query $query -SqlCredential $cred
    }
    catch{
        Write-Error "Something went wrong creating the test for $($view.Schema).$($view.Name)`n$_"
    }
}
#endregion