What you can do is the following:
- Create a linked server for each of your managed servers
- Create a view for the data you need from each linked server
Once you have created a linked server, you can query data from it as follows:
SELECT * FROM [linked-server].[database].[schema].[tablename]
Depending on what you need in your business case, you can create a view for each such table you want to query:
CREATE VIEW [ls1] AS SELECT * FROM [linked-server-1].[database].[schema].[tablename]; GO
CREATE VIEW [ls2] AS SELECT * FROM [linked-server-2].[database].[schema].[tablename]; GO
You can query them as you would a regular table:
SELECT * FROM [ls1];
SELECT * FROM [ls2];
Or you could create a view that combines all the data from all those linked servers:
CREATE VIEW [ls_all] AS
SELECT * FROM [linked-server-1].[database].[schema].[tablename]
SELECT * FROM [linked-server-2].[database].[schema].[tablename]
SELECT * FROM [linked-server-50].[database].[schema].[tablename]
You can then query all data from all linked servers as follows:
SELECT * FROM [ls_all];
You can use these views from a SQL Server job if need be.
If you have a table in your CMS containing all the information required to create the linked servers, then you can keep track of which linked servers/views you already created and in your job create linked servers and views for those that haven't been created yet (or for a select-it-all view, recreate it to also select for newly added linked-servers).
AFAICT there is no automated way to execute a query against of a group of servers that comes out of the box in SQL Server. Perhaps there exist tools that automate this for your? I haven't worked with any but they might exist.
But suppose you created linked servers for all 50 servers in your CMS, and you've created a view for each of the tables in those servers you want to query. Is the script that selects one after the other, as in:
...not good enough?