DECLARE @ServerName nvarchar(50) SET @ServerName='SQL01'; WITH CMSCTE AS ( --Anchor SELECT server_group_id, name, description, parent_id, 1 AS [Level], CAST((name) AS VARCHAR(MAX)) AS CMSPath FROM msdb.dbo.sysmanagement_shared_server_groups AS A WHERE parent_id IS NULL UNION ALL --Recursive Member SELECT B.server_group_id, B.name, B.description, B.parent_id, C.[level] + 1 AS [Level], CAST((C.CMSPath + '->' + B.Name) AS VARCHAR(MAX)) AS CMSPath FROM msdb.dbo.sysmanagement_shared_server_groups AS B JOIN CMSCTE AS C ON B.parent_id = C.server_group_id ) SELECT TOP 1 CMSPath AS 'Path in CMS' , B.name as 'Server Name', B.description AS 'Server Description', A.name AS 'Group Name', A.description AS 'Group Description' FROM CMSCTE AS A INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers AS B ON A.server_group_id=B.server_group_id WHERE B.name = @ServerName ORDER BY [Level] DESC
Monday, May 16, 2022
T-SQL to find Navigation path in CMS
In large enterprises there will be huge number of SQL servers which will be registered within a Central Management Server (CMS) and times it may become difficult to find out where the server is registered atleast for new team members in the Huge pile of servers and folder structure. This query give you the path where the specified servers is registered within the CMS.
Subscribe to:
Post Comments (Atom)
3 comments:
That's a great content, exactly what I was looking for. help with assignment Thank you and continue doing a good job!
imu affliated college
Marine courses
catering course fees
electro technical officer course
Post a Comment