Given this requirement:
I need a function, because I need to make joins with it inside my engine.
I would say that the requirement itself (that it needs to be a function) is misplaced given the stated goal (that the data needs to be joined with local tables and/or views). The issue is that TVFs (whether they be T-SQL or SQLCLR) behave in such a manner as to not be conducive to being joined. They:
- have a fixed estimated row count that could throw the optimizer off (T-SQL multi-statement TVF = 1 row, SQLCLR TVF = 1000 rows), and
- do not have any statistics on their columns.
For these two reasons, it is often best to dump the results to a local temporary table and then JOIN to that. So, you could accomplish this easily enough by doing the following:
- Create a stored procedure that:
- cycles through your list of servers and databases
- constructs a dynamic SQL string in your desired form of
'SELECT * FROM ' + sSRV + '.' + @sDB + '.MyTable' (of course, including a schema name before the table name ;-)
- executes that dynamic SQL
- in the main stored procedure:
- create a local temporary table to hold the results from the new stored procedure
INSERT INTO #LocalTempTable (column_list) EXEC dbo.NewStoredProcedure;
- optionally add indexes to #LocalTempTable
- Add joins to #LocalTempTable
This will get you what you want in a way that will perform better than the initial request, while also allowing for the list of servers and databases to fluctuate over time.