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;