Skip to content

Instantly share code, notes, and snippets.

@pollusb
Last active August 31, 2023 18:32
Show Gist options
  • Save pollusb/c82073662f28177a765aa5f9d64f1e4d to your computer and use it in GitHub Desktop.
Save pollusb/c82073662f28177a765aa5f9d64f1e4d to your computer and use it in GitHub Desktop.
Extract Version from stored procedure command text
function Get-DbaMaintenanceSolutionVersion {
param(
$SqlInstance,
$Database = 'master'
)
$query = "select object_definition(object_id) as SQLtxt from $Database.sys.procedures where name = 'DatabaseIntegrityCheck'"
foreach ($sql in $SqlInstance) {
$sp = Get-DbaDbStoredProcedure -SqlInstance $sql -Database $Database -Name DatabaseIntegrityCheck
if ($sp) {
$version = if ($sp.TextBody -match '--// Version: ([\d-]+ [\d\:]+)') {
$Matches[1]
} else {
$version = 'N/A'
}
} else {
$version = 'N/A'
}
[PSCustomObject]@{
SqlInstance = $sql
MaintenanceSolutionVersion = $version
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment