Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


SQL Server Central Management Servers System Tables

By:   |   Last Updated: 2011-05-23   |   Comments (4)   |   Related Tips: 1 | 2 | 3 | 4 | More > Central Management Servers


I have SQL Server Central Management Servers setup in my environment. How can I get a list of the registered servers and their associated properties? Are there any queries I can issue? Check out this tip to learn more.


Sometimes we came across a situation where we need to query the SQL Server Central Management Servers to get the list of the registered servers and groups. You can query the two MSDB system tables below to get a list of the registered servers and their groups.

  • msdb.dbo.sysmanagement_shared_registered_servers_internal
  • msdb.dbo.sysmanagement_shared_server_groups_internal

We can use these system tables for the following:

  • Find out the list of registered servers and their groups
  • Find out servers which are registered twice in different folders
  • Compare the query result to check out the servers registered in an incorrect group or decommissioned servers

Query to list server groups and types

USE msdb;
/* Query to list out Server Groups and Types */
FROM msdb.dbo.sysmanagement_shared_server_groups_internal;

query the sql server cms to get a list of  servers and groups

Query to list the registered servers

USE msdb ;
/* Query to list out the registered servers */
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal;

query to the list of registered servers

Query to list the server and groups

/* Servers and groups */
SELECT DISTINCT groups.name AS 'Server Group Name'
     ,svr.server_name AS 'Server Name'
FROM msdb.dbo.sysmanagement_shared_server_groups_internal groups 
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr
 ON groups.server_group_id = svr.server_group_id;

use queries from sql server cms

Next Steps

Last Updated: 2011-05-23

get scripts

next tip button

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Friday, March 10, 2017 - 8:37:20 AM - Greg Robidoux Back To Top

Thanks James.  The code has been updated.

Thursday, March 09, 2017 - 6:09:37 PM - James Back To Top


You have a typo in "Query to list the registered servers"

FROM msdb.dbo.sysmanagement_shared_registered_servers_intrnal;  <-- should be internal

Tuesday, May 24, 2011 - 1:38:51 AM - Jugal Back To Top

Hi Abhi,

CMS is SQL Server 2008 feature. You can register the lower SQL Server version in CMS, but you must need atleast 1 SQL Server 2008 instance. That's why you are not able to in lower versions.

You can query it in SQL Server 2008 only.


Jugal SHah



Monday, May 23, 2011 - 1:32:07 PM - Abhi Back To Top

Hi Jugal,

Thanks for this tip, but its working only in SQL 2008.

what about 2005 sql server?


Thanks in advance,


Learn more about SQL Server tools