There are native PowerShell cmdlets to export to CSV or text but I have not seen one that can easily export any SQL query output to a SQL Server table in bulk (Write-DbaDataTable function of dbatools does it for one table which I use in my solution below). I probably did not look too hard but I have rolled out my own nevertheless. Please ping me if you have done something similar.
Goal of Export-QueryToSQLTable:
Export query results to table(s) given
- One or more queries to run
- One or more instances to run against
- Specify whether the query is instance-level or database level to run against each database if needed
- Specify filtration criteria for databases to run against
- Allow complete control over which instance/database/table each query result should be exported to
- Create target tables on the fly if they do not already exist
- Ability to truncate and reload existing tables
- Ability to run in integrated security mode or with SQL user authentication
- Add additional capture related meta-data to output like Datetime stamp, Instance name etc
Uses of such an export function
There are numerous uses of exporting results of a query to a table that I can think of quickly (meaning, this is not a comprehensive list)
- Export data from queries for safe-keeping/later reference/archival
- Capture data periodically for monitoring something
- General persistence of aggregated/processed results
- Auditing
- Staging data as part of a bigger ETL process
- Testing and capturing test results
- Persist output to simplify working with data in unwieldy tools
- ….and on and on
What can be persisted?… “Anything”
In short, “anything“. As long as the query returns a single resultset, this function can persist it. There are some edge-cases but I will save that discussion for later.
Generally, if a query produces output, it can be persisted to a table on a specific instance/database/table of your choosing.
Glenn Berry’s DMV’s exported to tables:
I think it will pique your curiosity if I showed you the output of running Glenn Berry‘s (aka Dr. DMV) DMV queries and went from there. Glenn Berry’s work is well-known in the SQL community. Below is the screenshot of tables with the output of his queries (partial list in screenshot).
Now that I have got your attention, let us see the code I used to accomplish the above:
#This is a real-world example of how to setup a script, save it as a .ps1 file and kick off from
# SQL agent as a cmdline script or from the windows scheduler
#This saves all the DMV's in Glenn Berry's script to tables prefixed with DrDMV.
Import-Module dbatools
. D:\PowerShell\Export-DMVInformation.ps1
. D:\PowerShell\Export-QueryToSQLTable.ps1
$dmvQueriesFile = 'D:\GlennBerryDMVs\SQL Server 2014 Diagnostic Information Queries (June 2016).sql'
[string] $saveToInstance = ''
[string] $runOnInstance = @('') #Add more instances if needed!
[string] $saveToDatabase = 'DBAUtil'
[string] $saveToTablePrefix = 'DrDMV_'
#Get the list of all queries as objects from the DMV queries file
[PSObject[]] $queries = Parse-DMVFile `
-file $dmvQueriesFile
#Here we are overriding the "save to" Instance, Database, Schema, Table and TruncationBeforeSave option as part of the queries input!
#
[PSObject[]] $queries = $queries |
SELECT `
*,
@{Label="SaveResultsToInstance";Expression={$saveToInstance}},
@{Label="SaveResultsToDatabase";Expression={$saveToDatabase}},
@{Label="SaveResultsToSchema";Expression={'dbo'}},
@{Label="SaveResultsToTable";Expression={
($saveToTablePrefix +
$_.QueryNr.ToString().PadLeft(3,'0').ToString() +
'_' +
$_.QueryTitle
).Replace(' ','_').Replace('-','_')}},
@{Label="SaveResultsTruncateBeforeSave";Expression={[bool]$false}}
#Now $queries can be passed into the function call
$output = Export-QueryToSQLTable `
-Queries $queries `
-RunOnInstances $runOnInstance `
-RunIncludeDBs @() `
-RunExcludeDBs @() `
-RunExcludeAllSystemDBs: $true `
-RunExcludeAllUserDBs: $false `
-RunOnDBsWithOwner @() `
-RunOnDBsWithStatus @('Normal') `
-SaveResultsToInstance '' `
-SaveResultsToDatabase '' `
-SaveResultsToSchema 'dbo' `
-SaveResultsTruncateBeforeSave: $false
That was it! That code created around 70 tables with DMV output information as valuable as gold to a DBA, all in a matter of minutes. It might have as well been 700 or even 7000 queries and it would not have made any difference to the code.
We could have targeted multiple instances and specific databases within those instances if we wanted to. In this case, I prefixed all the table names with DrDMV but you could choose your own prefix/suffix.
Pre-requisites for Glenn Berry’s DMV’s export above
There are a few pre-requisites for the code above.
- Obviously, my function Export-QueryToSQLTable which is on GitHub at
https://github.com/SQLJana/Export-QueryToSQLTable - Parse-DMVFile function available within the Export-DMVInformation written by Sander Stad available on GitHub at https://github.com/sanderstad/Export-DMVInformation. Parse-DMVFile is a private function which you need to expose manually. You can use his module to export Glenn Berry’s DMV output to Excel. Checkout Sander’s article on how to do so. Thank you for the good work Sander!
- The all-encompassing dbatools module available at https://dbatools.io/
- Glenn Berry’s DMV’s query file for the appropriate SQL version available here.
The code samples in the function comments also include a sample on how to use SQL authentication instead of Windows authentication if you need to.
Exporting sp_WhoIsActive data to a table
Adam Machanic‘s sp_WhoIsActive is an indispensable tool in every DBA’s arsenal. For me, not a day goes by without using it. When you are collecting OS performance counters and want to correlate database session activity with that, sp_WhoIsActive can be very helpful if you captured its output at periodic intervals. It has more options than what meets the eye (check “sp_whoisactive @help =1”) but, you can now export the output to a table with code below.
I know that Adam has an option to capture the output to a table using the procedure itself but when you want to do so at will as part of a larger data capture, it is different. Below is the code used:
#Save results of sp_WhoIsActive to a table
[string] $saveToInstance = ''
[string] $runOnInstance = @('')
[string] $saveToDatabase = 'DBAUtil'
[string] $saveToSchema = 'dbo'
$query1 = New-Object -TypeName PSObject
$query1 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 1
$query1 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'sp_WhoIsActiveInfo'
$query1 | Add-Member -MemberType NoteProperty -Name Query -Value 'EXEC sp_WhoIsActive @show_own_spid = 1'
$query1 | Add-Member -MemberType NoteProperty -Name Description -Value 'Gets connected users/sessions'
$query1 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
#...These two queries were built above
$queries = @($query1)
#Now $queries can be passed into the function call
$output = Export-QueryToSQLTable `
-Queries $queries `
-RunOnInstances $runOnInstance `
-RunIncludeDBs @() `
-RunExcludeDBs @() `
-RunExcludeAllSystemDBs: $true `
-RunExcludeAllUserDBs: $false `
-RunOnDBsWithOwner @() `
-RunOnDBsWithStatus @('Normal') `
-SaveResultsToInstance $saveToInstance `
-SaveResultsToDatabase $saveToDatabase `
-SaveResultsToSchema $saveToSchema `
-SaveResultsTruncateBeforeSave: $false
Exporting sp_Blitz* data to tables
As a community leader with a huge following, I follow Brent Ozar and use the amazing freebies that his team gives away. The “First responder kit” includes several useful stored procedures for troubleshooting SQL Server performance. Just like sp_WhoIsActive, the output of sp_Blitz* procedures can be output to tables and the output can even be visualized in the provided PowerBI report. Again, you can call make the call via this function Export-QueryToSQLTable at your choosing if the sp_Blitz* data collection is part of a bigger data capture. Code sample is below:
#This example saves the sp_Blitz output to tables
Import-Module dbatools
. D:\PowerShell\Export-DMVInformation.ps1
. D:\PowerShell\Export-QueryToSQLTable.ps1
[string] $saveToInstance = ''
[string] $runOnInstance = ''
[string] $saveToDatabase = 'DBAUtil'
[string] $saveToSchema = 'dbo'
$query1 = New-Object -TypeName PSObject
$query1 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 1
$query1 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Blitz_Detailed'
$query1 | Add-Member -MemberType NoteProperty -Name Query -Value 'EXEC DBAUtil.dbo.sp_Blitz @bringthepain = 1'
$query1 | Add-Member -MemberType NoteProperty -Name Description -Value 'General recommendations based on instance/database settings'
$query1 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
$query2 = New-Object -TypeName PSObject
$query2 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 2
$query2 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Blitz_Summary'
$query2 | Add-Member -MemberType NoteProperty -Name Query -Value 'EXEC DBAUtil.dbo.sp_Blitz @summarymode = 1'
$query2 | Add-Member -MemberType NoteProperty -Name Description -Value 'General recommendations summary based on instance/database settings'
$query2 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
#...These two queries were built above
[PSObject[]] $queries = @($query1, $query2)
#Now $queries can be passed into the function call
$output = Export-QueryToSQLTable `
-Queries $queries `
-RunOnInstances @($runOnInstance) `
-RunIncludeDBs @() `
-RunExcludeDBs @() `
-RunExcludeAllSystemDBs: $true `
-RunExcludeAllUserDBs: $false `
-RunOnDBsWithOwner @() `
-RunOnDBsWithStatus @('Normal') `
-SaveResultsToInstance $saveToInstance `
-SaveResultsToDatabase $saveToDatabase `
-SaveResultsToSchema $saveToSchema `
-SaveResultsTruncateBeforeSave: $true `
-CreateOutputTableWarningAction 'Continue'
Capture output of ANY of your own queries
Now that you have seen samples of the most famous stored procedures, it is time to do your own data collection and it can be based on your own queries or any query that you use frequently.
#This examples show how to run your own custom queries and save the output to tables
Import-Module dbatools
. D:\PowerShell\Export-DMVInformation.ps1
. D:\PowerShell\Export-QueryToSQLTable.ps1
[string] $saveToInstance = ''
[string] $runOnInstance = ''
[string] $saveToDatabase = 'DBAUtil'
[string] $saveToSchema = 'dbo'
$query1 = New-Object -TypeName PSObject
$query1 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 1
$query1 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Log space usage'
$query1 | Add-Member -MemberType NoteProperty -Name Query -Value 'select * from sys.dm_db_log_space_usage'
$query1 | Add-Member -MemberType NoteProperty -Name Description -Value 'Gets log space usage for specific database (being run against)'
$query1 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $true
$query2 = New-Object -TypeName PSObject
$query2 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 2
$query2 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Misc_001_OS_Performance_Counters'
$query2 | Add-Member -MemberType NoteProperty -Name Query -Value (Get-Content -LiteralPath "D:\Queries\OSPerformanceCounters.sql" | Out-String)
$query2 | Add-Member -MemberType NoteProperty -Name Description -Value 'OS Performance counters'
$query2 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
#...These two queries were built above
[PSObject[]] $queries = @($query1, $query2)
#Now $queries can be passed into the function call
$output = Export-QueryToSQLTable `
-RunOnInstanceSqlCredential $runOnInstanceSqlCredential `
-SaveToInstanceSqlCredential $saveToInstanceSqlCredential `
-Queries $queries `
-RunOnInstances @($runOnInstance) `
-RunIncludeDBs @() `
-RunExcludeDBs @() `
-RunExcludeAllSystemDBs: $true `
-RunExcludeAllUserDBs: $false `
-RunOnDBsWithOwner @() `
-RunOnDBsWithStatus @('Normal') `
-SaveResultsToInstance $saveToInstance `
-SaveResultsToDatabase $saveToDatabase `
-SaveResultsToSchema $saveToSchema `
-SaveResultsTruncateBeforeSave: $false `
-CreateOutputTableWarningAction 'Continue'
I even did a extremely basic PowerBI chart in seconds using the captured data:
By the way, the SQL used for capturing OS performance counters is available here.
I think I have hammered the point. You want the source-code. It is on GitHub as I already said but I am also posting it here:
WordPress has an issue with PowerShell block comments. So, I am going to separate the code comments from the actual code below.
Code Comments
.SYNOPSIS
Runs given set of queries against one or more instances+databases (if query is db specific) and saves the results to tables
.DESCRIPTION
Given a set of queries to run, saves the results to tables (maybe for periodic data collection)
The queries should be provided as an object array with specific properties.
# -----------------
# Pre-requisites
# -----------------
# 1) SQLServer Module (Mandatory) - http://port1433.com/2017/04/26/installing-the-sql-server-module-from-the-powershell-gallery/
# 2) dbatools (Mandatory) - https://dbatools.io/
# 3) Export-DMVInformation (Optional) - https://github.com/sanderstad/Export-DMVInformation/blob/master/Export-DMVInformation.psm1
# 4) Glenn Berry's DMV's (Optional) - https://www.sqlskills.com/blogs/glenn/category/dmv-queries/
.PARAMETER Queries
The set of queries to run as an object arrary with each element having attributes
QueryNr, QueryTitle, Query, Description and DBSpecific (true/false)
You can use the function Parse-DMVFile in Export-DMVInformation reference in Pre-requisites above
Or, you can simply build a query list yourself by hand. See the first example
.EXAMPLE
[string] $saveToInstance = ''
[string] $runOnInstance = ''
[string] $saveToDatabase = 'DBAUtil'
[string] $saveToSchema = 'dbo'
#If you need to use custom SQL credentials instead of windows integrated security..get the user/pass interactively
$runOnInstanceSqlCredential = Get-Credential
$saveToInstanceSqlCredential = $runOnInstanceSqlCredential
# (or non-interactively)
#$secpasswd = ConvertTo-SecureString "" -AsPlainText -Force
#$runOnInstanceSqlCredential = New-Object System.Management.Automation.PSCredential ("testuser", $secpasswd)
#$saveToInstanceSqlCredential = $runOnInstanceSqlCredential
$query1 = New-Object -TypeName PSObject
$query1 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 1
$query1 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'sp_who_Info'
$query1 | Add-Member -MemberType NoteProperty -Name Query -Value 'EXEC sp_who'
$query1 | Add-Member -MemberType NoteProperty -Name Description -Value 'Gets connected users/sessions'
$query1 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
$query2 = New-Object -TypeName PSObject
$query2 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 2
$query2 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Log space usage'
$query2 | Add-Member -MemberType NoteProperty -Name Query -Value 'select * from sys.dm_db_log_space_usage'
$query2 | Add-Member -MemberType NoteProperty -Name Description -Value 'Gets log space usage for specific database (being run against)'
$query2 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $true
#...These two queries were built above
$queries = @($query1, $query2)
#Now $queries can be passed into the function call
$output = Export-QueryToSQLTable `
-RunOnInstanceSqlCredential $runOnInstanceSqlCredential `
-SaveToInstanceSqlCredential $saveToInstanceSqlCredential `
-Queries $queries `
-RunOnInstances @($runOnInstance) `
-RunIncludeDBs @() `
-RunExcludeDBs @() `
-RunExcludeAllSystemDBs: $true `
-RunExcludeAllUserDBs: $false `
-RunOnDBsWithOwner @() `
-RunOnDBsWithStatus @('Normal') `
-SaveResultsToInstance $saveToInstance `
-SaveResultsToDatabase $saveToDatabase `
-SaveResultsToSchema $saveToSchema `
-SaveResultsTruncateBeforeSave: $false
.EXAMPLE
#Save results of sp_WhoIsActive to a table
[string] $saveToInstance = ''
[string] $runOnInstance = ''
$saveToInstance = 'DESKTOP-UBBP7PP\SQL2016'
$runOnInstance = 'DESKTOP-UBBP7PP\SQL2016'
[string] $saveToDatabase = 'TEST'
[string] $saveToSchema = 'dbo'
$query1 = New-Object -TypeName PSObject
$query1 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 1
$query1 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'sp_WhoIsActiveInfo'
$query1 | Add-Member -MemberType NoteProperty -Name Query -Value 'EXEC sp_WhoIsActive @show_own_spid = 1'
$query1 | Add-Member -MemberType NoteProperty -Name Description -Value 'Gets connected users/sessions'
$query1 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
#...These two queries were built above
$queries = @($query1)
#Now $queries can be passed into the function call
$output = Export-QueryToSQLTable `
-Queries $queries `
-RunOnInstances @($runOnInstance) `
-RunIncludeDBs @() `
-RunExcludeDBs @() `
-RunExcludeAllSystemDBs: $true `
-RunExcludeAllUserDBs: $false `
-RunOnDBsWithOwner @() `
-RunOnDBsWithStatus @('Normal') `
-SaveResultsToInstance $saveToInstance `
-SaveResultsToDatabase $saveToDatabase `
-SaveResultsToSchema $saveToSchema `
-SaveResultsTruncateBeforeSave: $false
.EXAMPLE
#This is a real-world example of how to setup a script, save it as a .ps1 file and kick off from
# SQL agent as a cmdline script or from the windows scheduler
#This saves all the DMV's in Glenn Berry's script to tables prefixed with DrDMV.
#This script uses a SQL credential for instances to run/save against.
#Drop the SQL credential related parameters if the service account you will run as has adequate permissions.
Import-Module C:\GitHub\dbatools\dbatools.psd1
. D:\PowerShell\Export-DMVInformation.ps1
. D:\PowerShell\Export-QueryToSQLTable.ps1
$dmvQueriesFile = 'D:\Software\SQL Server Tools\GlenBerryDMVs\SQL Server 2014 Diagnostic Information Queries (June 2016).sql'
[string] $saveToInstance = ''
[string] $runOnInstance = ''
[string] $saveToDatabase = 'DBAUtil'
[string] $saveToTablePrefix = 'DrDMV_'
[PSObject[]] $queries = Parse-DMVFile `
-file $dmvQueriesFile
# (or non-interactively)
$secpasswd = ConvertTo-SecureString "" -AsPlainText -Force
$runOnInstanceSqlCredential = New-Object System.Management.Automation.PSCredential ("", $secpasswd)
$saveToInstanceSqlCredential = $runOnInstanceSqlCredential
#Here we are overriding the "save to" Instance, Database, Schema, Table and TruncationBeforeSave option as part of the queries input!
#
[PSObject[]] $queries = $queries |
SELECT `
*,
@{Label="SaveResultsToInstance";Expression={$saveToInstance}},
@{Label="SaveResultsToDatabase";Expression={$saveToDatabase}},
@{Label="SaveResultsToSchema";Expression={'dbo'}},
@{Label="SaveResultsToTable";Expression={
($saveToTablePrefix +
$_.QueryNr.ToString().PadLeft(3,'0').ToString() +
'_' +
$_.QueryTitle
).Replace(' ','_').Replace('-','_')}},
@{Label="SaveResultsTruncateBeforeSave";Expression={[bool]$false}}
#Now $queries can be passed into the function call
$output = Export-QueryToSQLTable `
-RunOnInstanceSqlCredential $runOnInstanceSqlCredential `
-SaveToInstanceSqlCredential $saveToInstanceSqlCredential `
-Queries $queries `
-RunOnInstances @($runOnInstance) `
-RunIncludeDBs @() `
-RunExcludeDBs @() `
-RunExcludeAllSystemDBs: $true `
-RunExcludeAllUserDBs: $false `
-RunOnDBsWithOwner @() `
-RunOnDBsWithStatus @('Normal') `
-SaveResultsToInstance '' `
-SaveResultsToDatabase '' `
-SaveResultsToSchema 'dbo' `
-SaveResultsTruncateBeforeSave: $false
.PARAMETER
One or more instances on which to run the query. For example @('DEVBox\DevInst','QABox\QAInst', 'MiscBox')
.INPUTS
Queries to run (with certain attributes - see examples)
.OUTPUTS
The instances/databases against which it ran, success/failure status and message + number or rows saved with target table info.
.EXAMPLE
#Get the queries from DMV file into an array
# (uses function Parse-DMVFile in Export-DMVInformation reference in Pre-requisites above)
$dmvQueriesFile = 'D:\Software\SQL Server Tools\GlenBerryDMVs\SQL Server 2014 Diagnostic Information Queries (June 2016).sql'
[string] $saveToInstance = ''
[string] $runOnInstance = ''
[string] $saveToDatabase = 'DBAUtil'
[string] $saveToSchema = 'dbo'
[PSObject[]] $queries = Parse-DMVFile `
-file $dmvQueriesFile
#
#Each query returned above will have the below properties
#
#Name MemberType Definition
#---- ---------- ----------
#DBSpecific NoteProperty bool DBSpecific=False
#Description NoteProperty example: SQL and OS Version information for current instance
#Query NoteProperty example: SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];
#QueryNr NoteProperty example: 1
#QueryTitle NoteProperty example: Version Info
$output = Export-QueryToSQLTable `
-Queries $queries `
-RunOnInstances @($runOnInstance) `
-RunIncludeDBs @() `
-RunExcludeDBs @() `
-RunExcludeAllSystemDBs: $true `
-RunExcludeAllUserDBs: $false `
-RunOnDBsWithOwner @() `
-RunOnDBsWithStatus @('Normal') `
-SaveResultsToInstance $saveToInstance `
-SaveResultsToDatabase $saveToDatabase `
-SaveResultsToSchema $saveToSchema `
-SaveResultsTruncateBeforeSave: $false
.EXAMPLE
#This example uses windows integrated security to run Glen Berry's DMV scripts
# and save them to tables with prefix DrDMV_
$dmvQueriesFile = 'D:\Software\SQL Server Tools\GlenBerryDMVs\SQL Server 2014 Diagnostic Information Queries (June 2016).sql'
[string] $runOnInstance = ''
[string] $saveToInstance = ''
[string] $saveToDatabase = 'DBAUtil'
[string] $saveToTablePrefix = 'DrDMV_'
#Similar to first example (read that for more info)
[PSObject[]] $queries = Parse-DMVFile `
-file $dmvQueriesFile
#Here we are overriding the "save to" Instance, Database, Schema, Table and TruncationBeforeSave option as part of the queries input!
#
[PSObject[]] $queries = $queries |
SELECT `
*,
@{Label="SaveResultsToInstance";Expression={$saveToInstance}},
@{Label="SaveResultsToDatabase";Expression={$saveToDatabase}},
@{Label="SaveResultsToSchema";Expression={'dbo'}},
@{Label="SaveResultsToTable";Expression={
($saveToTablePrefix +
$_.QueryNr.ToString().PadLeft(3,'0').ToString() +
'_' +
$_.QueryTitle
).Replace(' ','_').Replace('-','_')}},
@{Label="SaveResultsTruncateBeforeSave";Expression={[bool]$false}}
#Notice below how all of the "SaveResultsTo*" parameters are emtpy and yet it works because
# we pass that information in as part of the input Queries parameter
Export-QueryToSQLTable `
-Queries $queries `
-RunOnInstances @($runOnInstance) `
-RunIncludeDBs @() `
-RunExcludeDBs @() `
-RunExcludeAllSystemDBs: $true `
-RunExcludeAllUserDBs: $false `
-RunOnDBsWithOwner @() `
-RunOnDBsWithStatus @('Normal') `
-SaveResultsToInstance '' `
-SaveResultsToDatabase '' `
-SaveResultsToSchema '' `
-SaveResultsTruncateBeforeSave: $false
.EXAMPLE
#This example saves the sp_Blitz output to tables
Import-Module dbatools
. D:\PowerShell\Export-DMVInformation.ps1
. D:\PowerShell\Export-QueryToSQLTable.ps1
[string] $saveToInstance = ''
[string] $runOnInstance = ''
[string] $saveToDatabase = 'DBAUtil'
[string] $saveToSchema = 'dbo'
$query1 = New-Object -TypeName PSObject
$query1 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 1
$query1 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Blitz_Detailed'
$query1 | Add-Member -MemberType NoteProperty -Name Query -Value 'EXEC DBAUtil.dbo.sp_Blitz @bringthepain = 1'
$query1 | Add-Member -MemberType NoteProperty -Name Description -Value 'General recommendations based on instance/database settings'
$query1 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
$query2 = New-Object -TypeName PSObject
$query2 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 2
$query2 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Blitz_Summary'
$query2 | Add-Member -MemberType NoteProperty -Name Query -Value 'EXEC DBAUtil.dbo.sp_Blitz @summarymode = 1'
$query2 | Add-Member -MemberType NoteProperty -Name Description -Value 'General recommendations summary based on instance/database settings'
$query2 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
#...These two queries were built above
[PSObject[]] $queries = @($query1, $query2)
#Now $queries can be passed into the function call
$output = Export-QueryToSQLTable `
-Queries $queries `
-RunOnInstances @($runOnInstance) `
-RunIncludeDBs @() `
-RunExcludeDBs @() `
-RunExcludeAllSystemDBs: $true `
-RunExcludeAllUserDBs: $false `
-RunOnDBsWithOwner @() `
-RunOnDBsWithStatus @('Normal') `
-SaveResultsToInstance $saveToInstance `
-SaveResultsToDatabase $saveToDatabase `
-SaveResultsToSchema $saveToSchema `
-SaveResultsTruncateBeforeSave: $true `
-CreateOutputTableWarningAction 'Continue'
.EXAMPLE
#This examples show how to run your own custom queries and save the output to tables
Import-Module dbatools
. D:\PowerShell\Export-DMVInformation.ps1
. D:\PowerShell\Export-QueryToSQLTable.ps1
[string] $saveToInstance = ''
[string] $runOnInstance = ''
[string] $saveToDatabase = 'DBAUtil'
[string] $saveToSchema = 'dbo'
$query1 = New-Object -TypeName PSObject
$query1 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 1
$query1 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Log space usage'
$query1 | Add-Member -MemberType NoteProperty -Name Query -Value 'select * from sys.dm_db_log_space_usage'
$query1 | Add-Member -MemberType NoteProperty -Name Description -Value 'Gets log space usage for specific database (being run against)'
$query1 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $true
$query2 = New-Object -TypeName PSObject
$query2 | Add-Member -MemberType NoteProperty -Name QueryNr -Value 2
$query2 | Add-Member -MemberType NoteProperty -Name QueryTitle -Value 'Misc_001_OS_Performance_Counters'
$query2 | Add-Member -MemberType NoteProperty -Name Query -Value (Get-Content -LiteralPath "D:\Queries\OSPerformanceCounters.sql" | Out-String)
$query2 | Add-Member -MemberType NoteProperty -Name Description -Value 'OS Performance counters'
$query2 | Add-Member -MemberType NoteProperty -Name DBSpecific -Value $false
#...These two queries were built above
[PSObject[]] $queries = @($query1, $query2)
#Now $queries can be passed into the function call
$output = Export-QueryToSQLTable `
-RunOnInstanceSqlCredential $runOnInstanceSqlCredential `
-SaveToInstanceSqlCredential $saveToInstanceSqlCredential `
-Queries $queries `
-RunOnInstances @($runOnInstance) `
-RunIncludeDBs @() `
-RunExcludeDBs @() `
-RunExcludeAllSystemDBs: $true `
-RunExcludeAllUserDBs: $false `
-RunOnDBsWithOwner @() `
-RunOnDBsWithStatus @('Normal') `
-SaveResultsToInstance $saveToInstance `
-SaveResultsToDatabase $saveToDatabase `
-SaveResultsToSchema $saveToSchema `
-SaveResultsTruncateBeforeSave: $false `
-CreateOutputTableWarningAction 'Continue'
.NOTES
Version History
v1.0 - Jana Sattainathan - Jan.06.2017
.LINK
N/A
The actual code
WARNING: Recommend using the code on GitHub as the code below may be outdated!
function Export-QueryToSQLTable
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$false)]
[int64] $CaptureSetID = [int64]::Parse((Get-Date).ToString('yyyyMMddHHmmss')),
[Parameter(Mandatory=$false)]
[System.Management.Automation.PSCredential]$RunOnInstanceSqlCredential = $NULL,
[Parameter(Mandatory=$false)]
[System.Management.Automation.PSCredential]$SaveToInstanceSqlCredential = $NULL,
#See the comment description for the parameters in documentation above for specifics about each parameter
#This parameter is a custom object (PSObject) with certain minimum attributes expected! See comments above for usage examples
[Parameter(Mandatory=$true)]
[PSObject[]] $Queries,
[Parameter(Mandatory=$true)]
[string[]] $RunOnInstances,
[Parameter(Mandatory=$false)]
[string[]] $RunIncludeDBs = @(), #ALL
[Parameter(Mandatory=$false)]
[object[]] $RunExcludeDBs = @(),
[Parameter(Mandatory=$false)]
[switch] $RunExcludeAllSystemDBs = $true,
[Parameter(Mandatory=$false)]
[switch] $RunExcludeAllUserDBs = $false,
[Parameter(Mandatory=$false)]
[string[]] $RunOnDBsWithOwner = @(),
[Parameter(Mandatory=$false)]
[string[]] $RunOnDBsWithStatus = @('Normal'),
#If empty value is passed for this paramter, value should be specified as property of Queries input parameter
[Parameter(Mandatory=$false)]
[string] $SaveResultsToInstance = '',
#If empty value is passed for this paramter, value should be specified as property of Queries input parameter
[Parameter(Mandatory=$false)]
[string] $SaveResultsToDatabase = '',
#If empty value is passed for this paramter, value should be specified as property of Queries input parameter
[Parameter(Mandatory=$false)]
[string] $SaveResultsToSchema = 'dbo', #Schema must already exist!
[Parameter(Mandatory=$false)]
[switch] $SaveResultsTruncateBeforeSave = $false,
[Parameter(Mandatory=$false)]
[int] $QueryTimeout = 60,
[Parameter(Mandatory=$false)]
[int] $ConnectionTimeout = 5,
#If there was a warning reported when creating the output table, what should be done Stop, Continue or SilentlyContinue
[Parameter(Mandatory=$false)]
[string] $CreateOutputTableWarningAction = 'Stop'
)
[string] $fn = $MyInvocation.MyCommand
[string] $stepName = "Begin [$fn]"
[object] $returnObj = $null
[int64] $Global:captureSetLine = 1 #Has to be global to be usable in SELECT's (for row sequence value generation)
[string] $saveToInstanceName = ''
[string] $saveToDatabaseName = ''
[string] $saveToSchemaName = 'dbo'
[string] $saveToTableName = ''
[bool] $saveTruncateBeforeSave = $false
[bool] $skipPK = $false
[int] $instanceCounter = 0
[int] $queryCounter = 0
[int] $dbCounter = 0
[object[]] $specificDatabases = @()
[HashTable]$params = @{
'Queries (count)' = $Queries.Count;
'RunOnInstances' = $RunOnInstances;
'RunExcludeDBs' = $RunExcludeDBs;
'RunExcludeAllSystemDBs' = $RunExcludeAllSystemDBs;
'RunExcludeAllUserDBs' = $RunExcludeAllUserDBs;
'RunOnDBsWithOwner' = $RunOnDBsWithOwner;
'RunOnDBsWithStatus' = $RunOnDBsWithStatus;
'SaveResultsToInstance' = $SaveResultsToInstance;
'SaveResultsToDatabase' = $SaveResultsToDatabase;
'SaveResultsToSchema' = $SaveResultsToSchema;
'SaveResultsTruncateBeforeSave' = $SaveResultsTruncateBeforeSave;
'QueryTimeout' = $QueryTimeout;
'ConnectionTimeout' = $ConnectionTimeout;
'CaptureSetID' = $CaptureSetID}
try
{
$stepName = "[$fn]: Validate parameters"
#--------------------------------------------
Write-Verbose $stepName
$stepName = "Run of every instance specified"
#--------------------------------------------
Write-Host $stepName
foreach($runOnInstance in $RunOnInstances)
{
$instanceCounter++
$specificDatabases = @()
Write-Progress -Activity "Instances progress:" `
-PercentComplete ([int](100 * $instanceCounter / $RunOnInstances.Length)) `
-CurrentOperation ("Completed {0}%" -f ([int](100 * $instanceCounter / $RunOnInstances.Length))) `
-Status ("Working on [{0}]" -f $runOnInstance) `
-Id 1
$stepName = "Run each query and export results to table"
#--------------------------------------------
Write-Host $stepName
foreach ($query in $Queries)
{
$queryCounter++
$dbCounter = 0
$dataTable = $null
$Global:captureSetLine = 1 #Has to be global to be usable in SELECT's
$skipPK = $false
Write-Progress -Activity "DMV Queries progress:" `
-PercentComplete ([int](100 * $queryCounter / $Queries.Length)) `
-CurrentOperation ("Completed {0}%" -f ([int](100 * $queryCounter / $Queries.Length))) `
-Status ("Working on [{0}]" -f $query.QueryTitle) `
-Id 2 `
-ParentId 1
$stepName = "Get the list of qualifying databases"
#--------------------------------------------
Write-Host $stepName
#Default is to run against master if DMV is not DB specific
$databases = @('master')
#If DMV is DB specific, then we need to be more discerning
if ($query.DBSpecific -eq $true)
{
#Only fetch for the first time around for a specific query
if ($specificDatabases.Count -le 0)
{
$specificDatabases = Get-DbaDatabase `
-SqlInstance $runOnInstance `
-SqlCredential $RunOnInstanceSqlCredential `
-ExcludeDatabase $RunExcludeDBs `
-ExcludeAllUserDb: $RunExcludeAllUserDBs `
-ExcludeAllSystemDb: $RunExcludeAllSystemDBs `
-Status $RunOnDBsWithStatus `
-Owner $RunOnDBsWithOwner `
-Database $RunIncludeDBs
if ($specificDatabases.Count -eq 0)
{
Write-Error 'No specific databases qualified!'
}
}
$databases = $specificDatabases |
Select-Object Name -ExpandProperty Name
}
$stepName = "Run query on qualifying databases"
#--------------------------------------------
Write-Host $stepName
foreach($database in $databases)
{
$dbCounter++
Write-Progress -Activity "Databases progress for query:" `
-PercentComplete ([int](100 * $dbCounter / $databases.Length)) `
-CurrentOperation ("Completed {0}%" -f ([int](100 * $dbCounter / $databases.Length))) `
-Status ("Inner loop working on item [{0}]" -f $database) `
-Id 3 `
-ParentId 2
$stepName = "Decide on target schema/table names etc"
#--------------------------------------------
#If the input Query has target table information specified, use it, else generate using the query name
#InstanceName
#---------------------
if (($query | Get-Member | Select-Object Name | Where-Object{$_.Name.ToUpper() -eq 'SAVERESULTSTOINSTANCE'}) -ne $null)
{
$saveToInstanceName = $query.SaveResultsToInstance
}
else
{
$saveToInstanceName = $SaveResultsToInstance
}
if ($saveToInstanceName.Trim().Length -eq 0) { Throw "SaveResultsToInstance is empty. Specify it as a parameter or as an attribute with non-empty value in Queries input parameter! See examples for reference."}
#DatabaseName
#---------------------
if (($query | Get-Member | Select-Object Name | Where-Object{$_.Name.ToUpper() -eq 'SAVERESULTSTODATABASE'}) -ne $null)
{
$saveToDatabaseName = $query.SaveResultsToDatabase
}
else
{
$saveToDatabaseName = $SaveResultsToDatabase
}
if ($saveToDatabaseName.Trim().Length -eq 0) { Throw "SaveResultsToDatabase is empty. Specify it as a parameter or as an attribute with non-empty value in Queries input parameter! See examples for reference."}
#SchemaName
#---------------------
if (($query | Get-Member | Select-Object Name | Where-Object{$_.Name.ToUpper() -eq 'SAVERESULTSTOSCHEMA'}) -ne $null)
{
$saveToSchemaName = $query.SaveResultsToSchema
}
else
{
$saveToSchemaName = $SaveResultsToSchema
}
if ($saveToSchemaName.Trim().Length -eq 0) { Throw "SaveResultsToSchema is empty. Specify it as a parameter or as an attribute with non-empty value in Queries input parameter! See examples for reference."}
#TableName
#---------------------
if (($query | Get-Member | Select-Object Name | Where-Object{$_.Name.ToUpper() -eq 'SAVERESULTSTOTABLE'}) -ne $null)
{
$saveToTableName = $query.SaveResultsToTable
}
else
{
$saveToTableName = $query.QueryTitle -Replace "[#?\{\[\(\)\]\}\ \,\.\']", '_' #Replace junk with underscore!
}
if ($saveToTableName.Trim().Length -eq 0) { Throw "SaveResultsToTable is empty. Specify a non-empty QueryTitle for query or as an attribute with non-empty value in Queries input parameter! See examples for reference."}
#TruncateBeforeSave?
#---------------------
if (($query | Get-Member | Select-Object Name | Where-Object{$_.Name.ToUpper() -eq 'SAVERESULTSTRUNCATEBEFORESAVE'}) -ne $null)
{
$saveTruncateBeforeSave = $query.SaveResultsTruncateBeforeSave
}
else
{
$saveTruncateBeforeSave = $SaveResultsTruncateBeforeSave
}
$stepName = "Running query: [{0}] on [{1}]" -f $query.QueryTitle, $database
#--------------------------------------------
Write-Host $stepName
Write-Host '--------------------------------------------------'
$invokeParams = @{
ServerInstance = $runOnInstance
Query = $query.Query
Database = $database
QueryTimeout = $QueryTimeout
ConnectionTimeout = $ConnectionTimeout
As = "PSObject"
}
if ($RunOnInstanceSqlCredential) {$invokeParams.Add('Credential', $RunOnInstanceSqlCredential)}
$dataTable = Invoke-Sqlcmd2 @invokeParams
$rowCount = 0
if ($dataTable -ne $null)
{
$rowCount = @($dataTable).Count
}
#In case the consumer of this function needs to record details, we return a nicely packaged return object for each execution
$returnObj = New-Object PSObject
$returnObj | Add-Member -NotePropertyName 'CaptureSetID' -NotePropertyValue $CaptureSetID
$returnObj | Add-Member -NotePropertyName 'RunOnInstance' -NotePropertyValue $runOnInstance
$returnObj | Add-Member -NotePropertyName 'RunOnDatabase' -NotePropertyValue $database
$returnObj | Add-Member -NotePropertyName 'QueryTitle' -NotePropertyValue $query.QueryTitle
$returnObj | Add-Member -NotePropertyName 'Query' -NotePropertyValue $query.Query
$returnObj | Add-Member -NotePropertyName 'QueryDescription' -NotePropertyValue $query.Description
$returnObj | Add-Member -NotePropertyName 'SaveToInstance' -NotePropertyValue $saveToInstanceName
$returnObj | Add-Member -NotePropertyName 'SaveToDatabase' -NotePropertyValue $saveToDatabaseName
$returnObj | Add-Member -NotePropertyName 'SaveToSchema' -NotePropertyValue $saveToSchemaName
$returnObj | Add-Member -NotePropertyName 'SaveToTable' -NotePropertyValue $saveToTableName
$returnObj | Add-Member -NotePropertyName 'SaveToTruncateBeforeSave' -NotePropertyValue $saveTruncateBeforeSave
$returnObj | Add-Member -NotePropertyName 'RowCount' -NotePropertyValue $rowCount
$returnObj | Add-Member -NotePropertyName 'Status' -NotePropertyValue 'Success'
$returnObj | Add-Member -NotePropertyName 'StatusDescription' -NotePropertyValue '[None]'
$returnObj | Add-Member -NotePropertyName 'DateTimeStamp' -NotePropertyValue Get-Date
try
{
#Skip table if results are null or if there are no columns returned!
if ($rowCount -eq 0)
{
Write-Warning ("Null output was returned for query: [{0}]. Skipping." -f $query.QueryTitle)
}
else
{
$stepName = "Adding custom columns to output"
#--------------------------------------------
# Add additional columns that are of interest to identify instances/databases/runs/time etc
$resultsWAddlCols = $dataTable |
SELECT `
@{Label="CaptureSetID";Expression={[int64]$CaptureSetID}},
@{Label="CaptureSetLine";Expression={[int64]$Global:captureSetLine;$Global:captureSetLine++}},
@{Label="CaptureInstance";Expression={[string]$runOnInstance}},
@{Label="CaptureDB";Expression={[string]$database}},
@{Label="CaptureDate";Expression={Get-Date}},
*
$stepName = "Convert from object array to DataTable"
#--------------------------------------------
$dataTableWAddlCols = $resultsWAddlCols |
Out-DbaDataTable `
-WarningAction: SilentlyContinue #Supress warnings about columns whose datatypes cannot be converted
$stepName = "Saving to: [{0}.{1}]" -f $saveToSchemaName, $saveToTableName
#--------------------------------------------
Write-Host $stepName
$invokeParams = @{
SqlInstance = $saveToInstanceName
InputObject = $dataTableWAddlCols
Database = $saveToDatabaseName
Schema = $saveToSchemaName
Table = $saveToTableName
AutoCreateTable = $true
Truncate = $saveTruncateBeforeSave
#Need to stop if table cannot be created or something similar
WarningAction = $CreateOutputTableWarningAction
}
if ($SaveToInstanceSqlCredential) {$invokeParams.Add('SqlCredential', $SaveToInstanceSqlCredential)}
#WARNING: Write-DbaDataTable currently has a bug if a schema other than 'dbo' is specified resulting in "WARNING: [Write-DbaDataTable][22:05:00] Schema does not exist."
Write-DbaDataTable @invokeParams
#No need to create PK if we are looping through subsequent db's on the same instance for same query!
if ($skipPK -eq $false)
{
#Create a PK only if the table does not already have a PK (if user modified it but knows what he/she is doing, we dont care to be anal).
$stepName = "Check if PK exists on: [{0}]" -f $saveToTableName
#--------------------------------------------
$sql = "SELECT 1
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}'" -f $saveToTableName, $saveToSchemaName
#Splat inputs (except SQL) and run the sql
$invokeParams = @{
ServerInstance = $saveToInstanceName
Database = $saveToDatabaseName
QueryTimeout = $QueryTimeout
ConnectionTimeout = $ConnectionTimeout
As = "PSObject"
}
if ($SaveToInstanceSqlCredential) {$invokeParams.Add('Credential', $SaveToInstanceSqlCredential)}
$dataTable = Invoke-Sqlcmd2 @invokeParams -Query $sql
if ($dataTable.Count -eq 0)
{
#We need to create a PK on the table else subsequent Write-DbaDataTable will not APPEND data if it is still a HEAP
$stepName = "Creating PK on: [{0}]" -f $saveToTableName
#--------------------------------------------
Write-Host $stepName
foreach($sql in @(
(("ALTER TABLE {0} ALTER COLUMN CaptureSetID BIGINT NOT NULL `n" +
"ALTER TABLE {1} ALTER COLUMN captureSetLine BIGINT NOT NULL `n"
) -f $saveToTableName, $saveToTableName),
(("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY CLUSTERED (CaptureSetID, CaptureSetLine) `n"
) -f $saveToTableName, "PK_$saveToTableName")
))
{
#Run using the same spalt values as above (only SQL is different)
$dataTable = Invoke-Sqlcmd2 @invokeParams -Query $sql
}
}
$skipPK = $true
}
}
}
catch
{
#Ignore PK already exists error!
if ($_.Exception.ErrorCode -ne -2146232060)
{
Write-Error "Error in step [$stepName] during Table/PK creation: $($_.Exception.Message)"
}
$returnObj.Status = 'Error'
$returnObj.StatusDescription = "Error in step [$stepName]: $($_.Exception.Message)"
}
finally
{
$returnObj
}
}
}
}
$stepName = "Completed export!"
#--------------------------------------------
Write-Host $stepName
}
catch
{
[Exception]$ex = $_.Exception
Throw "Unable to export queries to database tables! Error in step: `"{0}]`" `n{1}" -f `
$stepName, $ex.Message
}
finally
{
#Return value if any
}
}
Deficiencies and opportunities for improvements:
There are lots of opportunities for improvements
- It can be slow if you run a lot of queries against a lot of instances although you can use my suggestions that will be immensely useful on how to parallelize a PowerShell workload
- If the SQL returns multiple resultsets like “sp_Help ‘sys.databases'” that will not work. Queries have to return a single Resultset.
If the columns returned change over time but if you are saving to an existing table, there could be issues. In other words, if DMV queries for various versions of SQL Server return different set of columns, they cannot all be persisted to a single output table.In the github version of the function, I have added support for changing columns in a query. Basically, only the columns that already exist in the table will be uploaded.- Based on the samples, if the query returned a NULL, no table will be created/output
It is not too difficult to add options to insert only the available attributes even if certain columns do not exist in the output that needs to be persisted.
Conclusion:
I have found this little function to be immensely useful even after my very first use. I am sure you will also find it useful too. Please feel free to update the latest code on GitHub and send me a pull request.
SQL Jana 
Dang, wow, you’re saving EVERYTHING to tables! Neat, cool project man.
Almost. Thanks Brent!
When I run the example code in powershell I get an error ” Error in step [Select only the columns already in the target table and ignore extra columns (if table exists):” The table does not exist so what am I doing wrong?
Hi Jeff, Sorry for the late reply. If by any chance you have any reference to Invoke-DBASqlCmd, can you please replace it with Invoke-SqlCmd2 and give it a try?
I’m getting the following error message from my script:
Parse-DMVFile : The term ‘Parse-DMVFile’ is not recognized as the name of a cmdlet,
function, script file, or operable program. Check the spelling of the name, or if a path was
included, verify that the path is correct and try again.
Hi Reginald. You need #2 pre-requisite listed in the blog section “Pre-requisites for Glenn Berry’s DMV’s export above”.
Hi SqlJana,
Thanks so much for this, it’s a great little tool. A sidenote: since dbatools 1.0, there are some breaking changes in the naming of the functions you use. I took the liberty of adjusting your function so that it works for me with the new version. It’s basically just Out-DbaDataTable replaced by ConvertTo-DbaDataTable and Write-DbaDataTable replaced by Write-DbaDbTableData.
Thanks again!
Thank you so much for your comment and for the note about the use of revised functions in dbatools. I am sure the readers would find your comment useful until I update and test the code in the post. Thanks again.