SQL Server Central Management Servers System Tables

By: Jugal Shah   |   Read Comments (2)   |   Related Tips: 1 | 2 | 3 | 4 | More > Central Management Servers

Problem

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.

Solution

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;
GO
/* Query to list out Server Groups and Types */
SELECT *
FROM msdb.dbo.sysmanagement_shared_server_groups_internal;
GO

Query to list the registered servers

USE msdb ;
GO
/* Query to list out the registered servers */
SELECT * 
FROM msdb.dbo.sysmanagement_shared_registered_servers_intrnal;
GO

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

Next Steps

• As you implement and manage more SQL Server Central Management Servers, consider using the queries from this tip.
• Check out the system tables referenced in this tip and see what other valuable nuggets of information you can discern.
• Check out more tips on Central Management Server on MSSQLTips.com
  • Execute same SQL Server query across multiple servers at the same time using Central Management Servers

About the author

Jugal has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips

We Recommend

• Execute SQL Server query on multiple servers at th...
• Run SQL Commands Against Multiple SQL Servers With...
• SQL Server Central Management Server Security...
• More SQL Server DBA Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code