fff5VHRk

1# Import the dbatools module
2Import-Module dbatools
3
4# Set the global values
5$instance = 'localhost,15789'
6$db = 'WideWorldImporters'
7$date = (Get-Date).ToString('yyyy-MM-dd')
8$creatorName = 'Beardy McBeardFace'
9$cred = Import-Clixml -Path D:\Creds\containersa.xml
10$TestClass = 'TestExistence'
11
12# Get the database object 
13$database = Get-DbaDatabase -SqlInstance $instance -Database $db -SqlCredential $cred
14
15#region Create TestClass
16$query = @"
17IF NOT EXISTS 
18(
19SELECT name FROM sys.schemas WHERE name = '$TestClass'
20)
21
22EXEC('CREATE SCHEMA [$TestClass]')
23
24IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = SCHEMA_ID('$TestClass') AND [name] = N'tSQLt.TestClass' AND [minor_id] = 0)
25EXEC sys.sp_addextendedproperty @name=N'tSQLt.TestClass', @value=1 , @level0type=N'SCHEMA',@level0name=N'$TestClass'
26"@
27
28    # Execute the query
29    Write-Host "Creating testclass $TestClass"
30    try{
31       # $query
32        Invoke-DbaSqlQuery -SqlInstance $instance -Database $db -Query $query -SqlCredential $cred
33    }
34    catch{
35        Write-Error "Something went wrong Creating testclass $TestClass`n$_"
36    }
37#endregion
38
39#region Stored procedures
40# Only select the procedures that are not a system object or TSQLt sps
41$procedures = $database.StoredProcedures | Where-Object {$_.IsSystemObject -eq $false -and $_.Schema -ne 'tSQLt'} | Select-Object Name, Schema
42
43# Loop through the objects
44foreach($procedure in $procedures){
45    # Setup the query
46    $query = "
47/*
48Description:
49Test if the stored procedure $($procedure.Schema).$($procedure.Name) exists
50
51Changes:
52Date		Who						Notes
53----------	---						--------------------------------------------------------------
54$date	$creatorName				Initial procedure
55*/
56CREATE PROCEDURE [$Testclass].[test If stored procedure $($procedure.Schema).$($procedure.Name) exists]
57AS
58BEGIN
59    SET NOCOUNT ON;
60
61    ----- ASSERT -------------------------------------------------
62    EXEC tSQLt.AssertObjectExists @ObjectName = N'$($procedure.Schema).$($procedure.Name)';
63
64END;
65    "
66    
67    # Execute the query
68    Write-Host "Creating test for $($procedure.Schema).$($procedure.Name)"
69    try{
70       # $query
71        Invoke-DbaSqlQuery -SqlInstance $instance -Database $db -Query $query -SqlCredential $cred
72    }
73    catch{
74        Write-Error "Something went wrong creating the test for $($procedure.Schema).$($procedure.Name)`n$_"
75    }
76}
77#endregion
78
79#region tables
80# Only select the procedures that are not a system object or TSQLt sps
81$tables = $database.tables | Where-Object {$_.IsSystemObject -eq $false -and $_.Schema -ne 'tSQLt'} | Select-Object Name, Schema
82
83# Loop through the objects
84foreach($table in $tables){
85    # Setup the query
86    $query = "
87/*
88Description:
89Test if the table $($table.Schema).$($table.Name) exists
90
91Changes:
92Date		Who						Notes
93----------	---						--------------------------------------------------------------
94$date	$creatorName				Initial procedure
95*/
96CREATE PROCEDURE [$Testclass].[test If table $($table.Schema).$($table.Name) exists]
97AS
98BEGIN
99    SET NOCOUNT ON;
100
101    ----- ASSERT -------------------------------------------------
102    EXEC tSQLt.AssertObjectExists @ObjectName = N'$($table.Schema).$($table.Name)';
103
104END;
105    "
106    
107    # Execute the query
108    Write-Host "Creating test for $($table.Schema).$($table.Name)"
109    try{
110       # $query
111        Invoke-DbaSqlQuery -SqlInstance $instance -Database $db -Query $query -SqlCredential $cred
112    }
113    catch{
114        Write-Error "Something went wrong creating the test for $($table.Schema).$($table.Name)`n$_"
115    }
116}
117#endregion
118
119#region views
120# Only select the procedures that are not a system object or TSQLt sps
121$views = $database.Views | Where-Object {$_.IsSystemObject -eq $false -and $_.Schema -ne 'tSQLt'} | Select-Object Name, Schema
122
123# Loop through the objects
124foreach($view in $views){
125    # Setup the query
126    $query = "
127/*
128Description:
129Test if the view $($view.Schema).$($view.Name) exists
130
131Changes:
132Date		Who						Notes
133----------	---						--------------------------------------------------------------
134$date	$creatorName				Initial procedure
135*/
136CREATE PROCEDURE [$Testclass].[test If view $($view.Schema).$($view.Name) exists]
137AS
138BEGIN
139    SET NOCOUNT ON;
140
141    ----- ASSERT -------------------------------------------------
142    EXEC tSQLt.AssertObjectExists @ObjectName = N'$($view.Schema).$($view.Name)';
143
144END;
145    "
146    
147    # Execute the query
148    Write-Host "Creating test for $($view.Schema).$($view.Name)"
149    try{
150       # $query
151        Invoke-DbaSqlQuery -SqlInstance $instance -Database $db -Query $query -SqlCredential $cred
152    }
153    catch{
154        Write-Error "Something went wrong creating the test for $($view.Schema).$($view.Name)`n$_"
155    }
156}
157#endregion