In playing with the latest CTP for SQL Server 2008, I stumbled across some new context menu items for a server group node in Registered Servers. You can now right-click a group node and choose options such as "New Query" or "Object Explorer." When I first saw the items in the list, I thought it was some kind of bug, because how can you launch a new query connected to a group of servers? Well, it does exactly that. In the new query window, you can say:
SELECT * FROM master.sys.tables;
And you will get a list of the tables on each server, with extra columns for [Server Name] and [Login]. Very useful, right?
However, a couple of things to note:
(a) for at least right now, do not mix versions in each server group, unless you only plan to run queries against objects that you know have identical schema. For example, if you try to run the above query against a group that includes both 2005 and 2008 instances, the resultsets can't be merged/unioned, because SQL Server 2008 has new columns.
(b) the resultset is sorted first by [Server Name]. I tried to add an ORDER BY clause that would allow me, for example, to select the highest values from DMVs across a set of servers, not caring about the order of the [Server Name]. It did not result in an error, but SSMS still injects its own ORDER BY clause that prevents the sorting of the data in any other way. The answer I received on Connect (Item #
315260): pull the data into Excel if you want to sort another way.