SQL Server: Tables Named the Same With Different Schemas in Same Database
Posted on 2014-02-05
If two tables are named the same in a database with different owners, and a schema is not specified when referencing that table, is there an order of precedence that is used in referencing the tables?
SELECT * FROM IPLookup
I have done some preliminary testing and it appears to go to the table owned by the default schema used by the current login, for a login with a single schema.
And I thought I recalled the ability for specifying multiple default schemas in SSMS, so I looked in SSMS and attempted to assign multiple default schemas to myself. I went to the Logon Properties window for my account, then the User Mapping tab. I selected the appropriate database, and then clicked the lookup button under the Default Schema option. The Select Schema window appeared, and I clicked the Choose Object button. I DID then choose multiple schemas (db_datareader and db_datawriter). Clicked OK, then OK. Back on the Logon Properties, only the first selected schema is listed under the Default Schema. So it appears to allow you to select more than one, but only actually accepts one?
In the example where there were tables owned by the owner and guest schemas above, what table would it go to if the logon did not specify a schema when refernecing the object and had a default schema of db_datareader?
Thanks In Advance