Sql-server – Create view using tables from differing databases on a server

dynamic-sqlsql servert-sqlview

I need to create a view that pulls data from multiple databases on a server. I've done this by hard coding the database names (db1.schema.table), but I need it to be more dynamic. This view needs to be available to run on multiple servers that have 1 or more databases. The database and table structures are all the same.

I know I can get the data using a cursor and dynamic SQL, but that does not result in the view I need for reporting in another software program.

If it makes more sense, I want to Union data from the same table in multiple databases into one view, however, the number and name of different databases will be different on different servers. Can this be done without hard coding the views on each server?

Best Answer

You'll need to hard-code the view on each server. But you can write a stored procedure to recreate the view on-demand.