3

I was wondering what the best way was to query multiple databases in a stored procedure. I can hard code the database names in the stored procedure i.e:

Select *
from [Audit].[dbo].[AuditTable] inner join
     [Core].[dbo].[CoreTable]
     on ....

But I would like to store the stored procedures in a version control system and then migrate them to different environments. So I was wondering the best way to do this. The database name is different for different environments.

Possible solutions:

  • Using a view to span tables in different databases? The database name will only exist in one place
  • Extended properties for the database/ passing the name into a stored procedure. It is possible to dynamically change the name of the database used in a stored procedure: Changing database name in a stored procedure dynamically But I am worried this will be messy
  • Separate environments with the same database name across all environments?

It seems that this is a duplicate of: Cross-database queries with different DB names in different environments

Community
  • 1
  • 1
D. Capon
  • 63
  • 4
  • What has your research yielded thus far? Can you provide links to show what you've researched, and indicate how each doesn't help you find your answer? – rory.ap Nov 22 '16 at 12:12

1 Answers1

0

Separate environments with the same database name across all environments seems like the cleanest option.

But if that is not an option, maybe substitution variables might work. SQL Server Data Tools for Visual Studio handles it this way - a referenced database is referred to using a token e.g. [Audit] would be replaced by [$(Audit)]. Then when you publish the database you define a value for each $variable and these values get substituted in place of the tokens.

Obviously this would require you to have a mechanism to make these replacements.

James Casey
  • 2,447
  • 1
  • 11
  • 19