Execute a query on servers in Central Management Server Group - Sethu's blog - Site Home

MultiServerQuery.ps1
# Scenario: Execute a query on servers listed in Central Managment Server Group
#
# How to use this powershell script:
# - Launch SQL Server PowerShell ( Start -> Run -> sqlps.exe)
# - Copy the following powershell script and paste it in powershell window

$serverGroupPath = 'SQLSERVER:\SQLRegistration\Central Management Server Group'

#
# recursively enumerate given server group and select unique list of server names
#
$instanceNameList = dir $serverGroupPath -recurse | where-object { $_.Mode.Equals("-") } | select-object Name -Unique

foreach($instanceName in $instanceNameList)
{
$results += Invoke-Sqlcmd -Query "select SERVERPROPERTY('ServerName') As ServerName, Name, crdate from sysdatabases" -ServerInstance $instanceName.Name
}

write-host '--------------------------------------------'
write-host 'All query results from all servers in group'
write-host '--------------------------------------------'
$results

write-host '-----------------------------------------------------------------------'
write-host 'List of databases on all servers in group that were created in last week'
write-host '-----------------------------------------------------------------------'
$results | where-object { $_.crdate -ge [DateTime]::Now.AddDays(-7) }

# This posting is provided "AS IS" with no warranties, and confers no rights.
# Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm