I need to create a view or a table valued function that returns one result set from a query ran against a dynamic list of databases (stored in a table). All of the databases have the same structure, and the view/tvf should contain the equivalent of a union all of the query for each database in the databases_table. The big-picture of all of this is that I need the results of this query to be available via ODBC.
After a lot of looking online, I've come up with a handful of potential solutions, just none that perfectly fit what I'm trying to accomplish.
The first thing that I looked into was having a view with a select for each distinct database, all unioned together. This worked perfectly, except for the fact that it is as far away from being maintainable as I can possibly imagine. I will have 25-100 of these sorts of multi-database queries, all of which have to be ran against a constantly changing set of databases.
I am still looking at possibly using this method, and making a stored procedures to dynamically create the views for me, but this is still not very useful, as I have been asked to make this entire process hands-free. Doing it this way would require someone to trigger the view update before we attempt to connect to the views via ODBC. I would like to avoid this if at all possible.
The second thing that I looked into got me a lot closer to what I needed. I made use of the sp_msforeachdb stored procedure, checked to see if the database was on the list, and if so I added the results of the query to a table variable. This works just fine, but the issue was that the dynamic sql is making it impossible for me to wrap it into a table valued function or view...
Declare @RETURNTABLE Table(variable1 varchar(20), variable 2 varchar(30))
INSERT INTO @RETURNTABLE
exec sp_msforeachdb 'IF "?" IN (SELECT DatabaseName FROM DatabaseList) BEGIN SELECT "?" [DatabaseName], variable1 , variable2 from [?].dbo.myTable END'
SELECT * FROM @DBINFO
This works perfectly when running it from the query from ms ssms, but as I've said, putting it in a veiw or tvf has proven beyond my ability. As far as I am aware, the dynamic SQL precludes the use of tvf's and views aren't able to work with variables.
Is there some way that I can make the results of the above code available via ODBC. Is there an alternative solution that I'm missing?
As far as I can tell a stored proc using dynamic SQL is your only choice.