To clarify, say we have three tables:
If my current-logged in user's default schema is schemaA and the default schema for the database is schemaB. And we create a stored procedure named dbo.SomeStoredProcedure that does this:
SELECT * FROM MyTable
It will actually query from dbo.MyTable, not schemaA.MyTable or schemaB.MyTable.
If someone could explain this behavior, I would appreciate it.
for objects not qualified with the schema name,SQL Server looks for the object in the default schema your user belongs to first, then in dbo
schema. if my default schema is SchemaC, SchemaA and SchemaB are not even checked to see if the object exists... only SchemaC then
In your procedure, you would simply see that behaviour being applied: objects not qualified with the schema name will assume dbo, which is the schema of the containing object and if it doesn't exist, would fail with object not found.
edit: corrected because my assumptions were way way wrong! Thanks Lynn Pettis!
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!