Applies to SQL Server 2008 R2.
We have several servers hosting several operational data stores and I wanted a list of which server hosted which ODS (one is hosted per country, and there are currently 10 countries). The way the servers are thought of is that the very first one that saw production is the "master", and the others scaled out to over time are the "slaves". The slaves are numbered, for example "ABC_SLAVE01_DEF", "ABC_SLAVE02_DEF". The servers are connected to via linked servers, and in turn the linked servers are connected to server aliases.
The script returns the name of the instance hosting the ODS, the linked server name (which matches the alias set up under SQL Server Configuration Manager), and the ODS database name.
Thanks to Irene Daniel for improving my original script by adding the test to only include databases that are online.