T-SQL to get Machine name from SQL Server Instance Name | SansSQL

Friday, February 28, 2014

T-SQL to get Machine name from SQL Server Instance Name

How many of you use Central Management Server?
How many of you use CMS for just storing the list of servers?
How many of you use CMS for other activities other thank just storing the server names?

The list of servers registered under the CMS can be queried using the view "sysmanagement_shared_registered_servers" present in msdb database.

This will give the list of SQL Server Instance names, but when we require to get the Machine name from the SQL Server instance name, we can use this query to achieve it.

SELECT DISTINCT CASE WHEN CHARINDEX('\',server_name) = 0 THEN server_name
      ELSE SUBSTRING(server_name,1,CHARINDEX('\',server_name)-1) 
      END AS MachineName
FROM msdb.dbo.sysmanagement_shared_registered_servers

No comments:

Post a Comment