Execute a query on servers in Central Management Server Group


# 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 ‘——————————————–‘

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