When working in SQL server, if you call an object--EXEC, SELECT FROM, etc, you can do so without specifying a schema and it will default to dbo (unless you have assigned a different default schema to that user, though it is more common not to). What happens if you have a procedure in, let's say, a dev schema and you are calling tables from both the dev and dbo schemas inside it? If you are including the schema names in your query <SchemaName.TableName> then everything will work as expected, but what happens if you exclude the schema from a table you are calling in hopes that it will return dbo? That depends on where the table exists, in dev, dbo, or both and what schema the calling object belongs to.

I wanted to put together a script that explains the behavior of default schema and schema scope in stored procedures. Let's walk through the following script for a more meaningful explanation.

--======================================================
-- Default Schema and Schema Scope in Stored Procedures 
--======================================================

-- Create a dev schema if you don't have one already

CREATE SCHEMA [dev] AUTHORIZATION [dbo];
GO

--DROP TABLE IF EXISTS dbo.ordertest;
--DROP TABLE IF EXISTS dev.ordertest;

-- Create a table in the dbo schema

CREATE TABLE dbo.ordertest (
ID smallint,
Targets varchar(20) );

INSERT INTO dbo.ordertest
SELECT 1, 'dbo';

-- Create the same table, this time in the dev schema

CREATE TABLE dev.ordertest (
ID smallint,
Targets varchar(20) );

INSERT INTO dev.ordertest
SELECT 1, 'dev';
GO

-- Notice we populate the dbo.ordertest with the targets value of dbo and the the dev.ordertest with dev

-- Now we need to create a stored procedure in the dev schema. We create this without specifying the schema in our SELECT table. 

CREATE PROCEDURE dev.TestSchema AS
    SET NOCOUNT ON

        BEGIN
        SELECT * FROM ordertest -- No schema specified here
        END;
GO

-- Now lets do a simple query against ordertest without specifying a schema

SELECT * FROM ordertest

-- The result is dbo, because dbo is the default schema in SQL

-- This time we specify the dev schema and get the value from our dev table

SELECT * FROM dev.ordertest

-- What happenes when we call our stored procedure in dev, which performs a SELECT against the table without a schema?

EXEC dev.TestSchema;

-- Returns dev, because the scope of the stored procedure is the dev schema, so it assumes an undefined schema is the same as the object calling it.

So if we have two tables of the same name in a SQL database that belong to two different schemas, if you call one of those in a SELECT without specifying the schema you will get the table in the dbo schema because that is default schema in SQL. If you write that same SELECT without specifying a schema and drop it in a stored procedure that belongs to the dev schema, when you execute that stored procedure it will return the results from the dev table because dev is the schema scope for that procedure. This can be confusing if you are expecting the result from dbo since that is the behavior you are used to. Now what happens if we delete the table in dev and call the procedure again?

-- What happenes if we delete the dev table and then run the procedure again?

DROP TABLE IF EXISTS dev.ordertest;

EXEC dev.TestSchema;

-- Now it returns dbo.

Now the query returns the result from dbo because the table no longer exists in the dev schema (the scope of the stored procedure) so it once again defaults to dbo. One more reason to always include schema names in all of our queries, that way you never run in to the situation we have illustrated here.

Data

Default Schema and Schema Scope in Stored Procedures

Nisor ness