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

Skip to main content