Central Management Server - Get List of Managed Servers

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Not Supported
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:   Unknown
Date:       14 Dec 2010

Description

Retrieves a list of servers and groups managed by the Central Management Server
 
NOTE:  Must be run from the CMS server or one of the managed servers.

Code

 -- note:  must be run from one of the managed servers connected to the CMS server instance

SELECT
  
server_id                   AS server_id,
  
server_groups.name          AS 'server_group_name',
  
server_name                 AS server_name,
  
shared_servers.description  AS 'server_description',
   (
      
SELECT name
      
FROM msdb.dbo.sysmanagement_shared_server_groups AS shared_groups
      
WHERE shared_groups.is_system_object = 1
      
AND shared_servers.server_type = shared_groups.server_type
  
) AS 'server_type_name'
FROM msdb.dbo.sysmanagement_shared_registered_servers AS shared_servers
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups AS server_groups
  
ON shared_servers.server_group_id = server_groups.server_group_id;

 


Comments