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;
|