0

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?

  • 3
    You'll probably get better answers if you post the code you've written so far, along with why the results are incorrect. Writing this out as a word problem isn't really helpful. – Erik Darling Sep 20 '17 at 20:03
  • I'm not sure you can accomplish it in a view but you could put in a stored procedure and use sp_msforeachdb to accomplish that. You'll need to write some dynamic sql and write code for which databases to skip . – indiri Sep 20 '17 at 20:10
  • @indiri sp_msforeachdb has a known bug that may result in all databases not being returned. Bad habits to kick : relying on undocumented behavior – thundercougarfalcon Sep 29 '17 at 22:55
1

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

  • Thanks! That's what I thought, just wanted to ask here to see if there was some possibility I didn't know about. – Chris Sep 20 '17 at 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.