Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Run a query against multiple databases by querying groups of instances listed in the CMS in parallel, then output a spreadsheet
# Run a query against multiple databases
# by querying groups of instances
# listed in the CMS in parallel,
# then output a spreadsheet
#
# Modules used: PoshRSJob, dbatools, ImportExcel
#
# Stop & remove all PoshRSJobs - Are you sure you want to do this?
# Get-RSJob | Stop-RSJob; Get-RSJob | remove-rsjob
# Use CMS server and optional group name to identify SQL instance names, remove duplicates
$sqlInstances = Get-DbaRegServer -SqlInstance MYCMS.my.domain -Group 'Prod1' | Select-Object -ExpandProperty ServerName | Sort-Object -Unique
$sqlLikeDbName = "specialDbNameToMatchGoesHere"
$dbNameMatches = $sqlInstances | Invoke-DbaQuery -database master -Query " select @@SERVERNAME as SQLInstance, name, state_desc, user_access_desc, is_read_only from sys.databases where database_id > 4 and state_desc = 'ONLINE' AND name LIKE `'%$($sqlLikeDbName)%`' ;" -Verbose -MessagesToOutput
# all SMO - no modules needed to import into the runspace
$results = Start-RSJob -Throttle 30 <# -ModulesToImport dbatools #> -InputObject $dbNameMatches <# -Verbose #> -ScriptBlock {
$ServerInstance = $_.SqlInstance
$database = $_.name
$smoSrv = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
# query from https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver15
$sqlQuery = @"
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 90
ORDER BY modify_date DESC
"@
$smoSrv.Databases[$database].ExecuteWithResults($sqlQuery).Tables[0]
} | Wait-RSJob -ShowProgress <#-Verbose#> | Receive-RSJob <#-Verbose#>
Write-Output "$(($results).count) rows returned"
$fileDescription = 'SQL Agent Jobs in Prod1, Stage1'
$filenameExcel = ("\\fileserver47.my.domain\dbasql\xlsx\" + [string](Get-Date -format "yyyy-MM-dd__HHmmss") + "_" + $fileDescription + ".xlsx" ) #assign $filename variable
$paramHash = @{
Path = $filenameExcel
WorksheetName = $fileDescription
TableName = $fileDescription
TableStyle = 'Medium27' # because I like blue
AutoSize = $True
}
$results | Export-Excel @paramHash -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors # exclude certain properties from creating columns
Write-Output "$filenameExcel exported"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment