Skip to content

Commit 30e2044

Browse files
SQLJanaSQLJana
authored
Updated samples
1 parent 55f2de4 commit 30e2044

1 file changed

Lines changed: 137 additions & 0 deletions

File tree

‎Export-QueryToSQLTable.ps1‎

Lines changed: 137 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,43 @@
7777
-SaveResultsToSchema $saveToSchema `
7878
-SaveResultsTruncateBeforeSave: $false
7979
80+
.EXAMPLE
81+
#Save results of sp_WhoIsActive to a table
82+
83+
[string] $saveToInstance = '<YOUR_INSTANCE_NAME>'
84+
[string] $runOnInstance = '<YOUR_INSTANCE_NAME>'
85+
86+
$saveToInstance = 'DESKTOP-UBBP7PP\SQL2016'
87+
$runOnInstance = 'DESKTOP-UBBP7PP\SQL2016'
88+
89+
[string] $saveToDatabase = 'TEST'
90+
[string] $saveToSchema = 'dbo'
91+
92+
$query1 = New-Object -TypeName PSObject
93+
$query1 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 1
94+
$query1 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'sp_WhoIsActiveInfo'
95+
$query1 | Add-Member -MemberType NoteProperty -Name Query -Value 'EXEC sp_WhoIsActive @show_own_spid = 1'
96+
$query1 | Add-Member -MemberType NoteProperty -Name Description -Value 'Gets connected users/sessions'
97+
$query1 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
98+
99+
#...These two queries were built above
100+
$queries = @($query1)
101+
102+
#Now $queries can be passed into the function call
103+
$output = Export-QueryToSQLTable `
104+
-Queries $queries `
105+
-RunOnInstances @($runOnInstance) `
106+
-RunIncludeDBs @() `
107+
-RunExcludeDBs @() `
108+
-RunExcludeAllSystemDBs: $true `
109+
-RunExcludeAllUserDBs: $false `
110+
-RunOnDBsWithOwner @() `
111+
-RunOnDBsWithStatus @('Normal') `
112+
-SaveResultsToInstance $saveToInstance `
113+
-SaveResultsToDatabase $saveToDatabase `
114+
-SaveResultsToSchema $saveToSchema `
115+
-SaveResultsTruncateBeforeSave: $false
116+
80117
.EXAMPLE
81118
82119
#This is a real-world example of how to setup a script, save it as a .ps1 file and kick off from
@@ -233,6 +270,106 @@
233270
-SaveResultsToDatabase '' `
234271
-SaveResultsToSchema '' `
235272
-SaveResultsTruncateBeforeSave: $false
273+
274+
.EXAMPLE
275+
276+
#This example saves the sp_Blitz output to tables
277+
Import-Module dbatools
278+
279+
. D:\PowerShell\Export-DMVInformation.ps1
280+
. D:\PowerShell\Export-QueryToSQLTable.ps1
281+
282+
[string] $saveToInstance = '<YOUR_INSTANCE_NAME>'
283+
[string] $runOnInstance = '<YOUR_INSTANCE_NAME>'
284+
[string] $saveToDatabase = 'DBAUtil'
285+
[string] $saveToSchema = 'dbo'
286+
287+
$query1 = New-Object -TypeName PSObject
288+
$query1 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 1
289+
$query1 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Blitz_Detailed'
290+
$query1 | Add-Member -MemberType NoteProperty -Name Query -Value 'EXEC DBAUtil.dbo.sp_Blitz @bringthepain = 1'
291+
$query1 | Add-Member -MemberType NoteProperty -Name Description -Value 'General recommendations based on instance/database settings'
292+
$query1 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
293+
294+
$query2 = New-Object -TypeName PSObject
295+
$query2 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 2
296+
$query2 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Blitz_Summary'
297+
$query2 | Add-Member -MemberType NoteProperty -Name Query -Value 'EXEC DBAUtil.dbo.sp_Blitz @summarymode = 1'
298+
$query2 | Add-Member -MemberType NoteProperty -Name Description -Value 'General recommendations summary based on instance/database settings'
299+
$query2 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
300+
301+
302+
#...These two queries were built above
303+
[PSObject[]] $queries = @($query1, $query2)
304+
305+
306+
#Now $queries can be passed into the function call
307+
$output = Export-QueryToSQLTable `
308+
-Queries $queries `
309+
-RunOnInstances @($runOnInstance) `
310+
-RunIncludeDBs @() `
311+
-RunExcludeDBs @() `
312+
-RunExcludeAllSystemDBs: $true `
313+
-RunExcludeAllUserDBs: $false `
314+
-RunOnDBsWithOwner @() `
315+
-RunOnDBsWithStatus @('Normal') `
316+
-SaveResultsToInstance $saveToInstance `
317+
-SaveResultsToDatabase $saveToDatabase `
318+
-SaveResultsToSchema $saveToSchema `
319+
-SaveResultsTruncateBeforeSave: $true `
320+
-CreateOutputTableWarningAction 'Continue'
321+
322+
.EXAMPLE
323+
324+
#This examples show how to run your own custom queries and save the output to tables
325+
326+
Import-Module dbatools
327+
328+
. D:\PowerShell\Export-DMVInformation.ps1
329+
. D:\PowerShell\Export-QueryToSQLTable.ps1
330+
331+
332+
[string] $saveToInstance = '<YOUR_INSTANCE_NAME>'
333+
[string] $runOnInstance = '<YOUR_INSTANCE_NAME>'
334+
[string] $saveToDatabase = 'DBAUtil'
335+
[string] $saveToSchema = 'dbo'
336+
337+
338+
$query1 = New-Object -TypeName PSObject
339+
$query1 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 1
340+
$query1 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Log space usage'
341+
$query1 | Add-Member -MemberType NoteProperty -Name Query -Value 'select * from sys.dm_db_log_space_usage'
342+
$query1 | Add-Member -MemberType NoteProperty -Name Description -Value 'Gets log space usage for specific database (being run against)'
343+
$query1 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $true
344+
345+
$query2 = New-Object -TypeName PSObject
346+
$query2 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 2
347+
$query2 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Misc_001_OS_Performance_Counters'
348+
$query2 | Add-Member -MemberType NoteProperty -Name Query -Value (Get-Content -LiteralPath "D:\Queries\OSPerformanceCounters.sql" | Out-String)
349+
$query2 | Add-Member -MemberType NoteProperty -Name Description -Value 'OS Performance counters'
350+
$query2 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
351+
352+
#...These two queries were built above
353+
[PSObject[]] $queries = @($query1, $query2)
354+
355+
356+
#Now $queries can be passed into the function call
357+
$output = Export-QueryToSQLTable `
358+
-RunOnInstanceSqlCredential $runOnInstanceSqlCredential `
359+
-SaveToInstanceSqlCredential $saveToInstanceSqlCredential `
360+
-Queries $queries `
361+
-RunOnInstances @($runOnInstance) `
362+
-RunIncludeDBs @() `
363+
-RunExcludeDBs @() `
364+
-RunExcludeAllSystemDBs: $true `
365+
-RunExcludeAllUserDBs: $false `
366+
-RunOnDBsWithOwner @() `
367+
-RunOnDBsWithStatus @('Normal') `
368+
-SaveResultsToInstance $saveToInstance `
369+
-SaveResultsToDatabase $saveToDatabase `
370+
-SaveResultsToSchema $saveToSchema `
371+
-SaveResultsTruncateBeforeSave: $false `
372+
-CreateOutputTableWarningAction 'Continue'
236373
237374
.NOTES
238375

0 commit comments

Comments
 (0)