Sethu's blog

dev@sqlservr.microsoft

Execute a query on servers in Central Management Server Group

Execute a query on servers in Central Management Server Group

  • Comments 1
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
Comments
Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post