default schema synonym
I have a database FooDb
with a schema BarSchema
that contains a table Tbl
(i.e. FooDb.BarSchema.Tbl
)
I am also logged in as a user with BarSchema
as default.
This query works fine
SELECT * FROM FooDb..Tbl
I also have a synonym for this table in another db
CREATE S开发者_开发技巧YNONYM TblSynonym FOR FooDb..Tbl
But now I get an error "Invalid object name 'FooDb..Tbl'" when executing
SELECT * FROM TblSynonym
If i change the synonym to
CREATE SYNONYM TblSynonym FOR FooDb.BarSchema.Tbl
it works fine.
Why doesn't the default schema work in synonyms?
(The background is that I'm consoldating data from several databases which all got same table names but different schema names. It would be a lot easier if I could set the default schema for each database on the user and then ignore it everywhere in the script)
The documentation suggests the db..tbl
syntax should work:
schema_name_2 Is the name of the schema of the base object. If schema_name is not specified the default schema of the current user is used.
This works for me in SQL Server 2008:
create synonym TestSynonym for TestDB..TestTable
One cause might be that the default schema is associated with the user, not the database. Check if your user has an unexpected default schema? In my SSMS, that setting is located under Database -> Security -> Users -> Properties.
精彩评论