Geek Answers Handbook

Create a synonym for the database / change the database viewpoint to

I know that databases are not supported CREATE SYNONYM, but I am looking to achieve the functionality that this would provide.

We have database A, which contains table views in database B. The problem is that “Database B” is not always called “Database B”. We use database projects for deployments that currently fail with the "Invalid object name" error if there is no "database B".

The workaround for now is to open the .dbschema file and find and replace. I suppose another option is to create a synonym loading table.

What is the best way to change the database to reference the number of views without changing each view separately?

thank

+5
source share
2 answers

Synonyms are a good way to do this. You have to create synonyms at the object level, though (as you discovered). An easy way to do this would be to write a script that goes through the list of tables in DatabaseB (from your example) and creates a synonym for everyone in DatabaseA. Keep the synonym name so that the code in your views does not change. For example, you have tbl_a, tbl_b and tbl_c in DatabaseB, you want your script to do the following as a result:

create synonym [otherDb].[tbl_a] for [DatabaseB].[schemaB].[tbl_a]
create synonym [otherDb].[tbl_b] for [DatabaseB].[schemaB].[tbl_b]
create synonym [otherDb].[tbl_c] for [DatabaseB].[schemaB].[tbl_c]

[otherDb]. [tbl_a], [otherDb]. [tbl_b] [otherDb]. [tbl_c]. , .

+5

. , . , , .

. . Powershell, SMO, . , .

, . , LIKE, "my_schema" "%", my_schema.

script. script SSMS .

, . , .. , , .

+2

More articles:


All Articles