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;