set default schema for a sql query

I do not believe there is a "per query" way to do this. (You can use the use keyword to specify the database - not the schema - but that's technically a separate query as you have to issue the go command afterward.)

Remember, in SQL server fully qualified table names are in the format:

[database].[schema].[table]

In SQL Server Management Studio you can configure all the defaults you're asking about.

• You can set up the default database on a per-user basis (or in your connection string):

  Security > Logins > (right click) user > Properties > General

• You can set up the default schema on a per-user basis (but I do not believe you can configure it in your connection string, although if you use dbo that is always the default):

  Security > Logins > (right click) user > Properties > User Mapping > Default Schema

In short, if you use dbo for your schema, you'll likely have the least amount of headaches.

A quick google pointed me to this page. It explains that from sql server 2005 onwards you can set the default schema of a user with the ALTER USER statement. Unfortunately, that means that you change it permanently, so if you need to switch between schemas, you would need to set it every time you execute a stored procedure or a batch of statements. Alternatively, you could use the technique described here.

If you are using sql server 2000 or older this page explains that users and schemas are then equivalent. If you don't prepend your table name with a schema\user, sql server will first look at the tables owned by the current user and then the ones owned by the dbo to resolve the table name. It seems that for all other tables you must prepend the schema\user.

Another way of adding schema dynamically or if you want to change it to something else

DECLARE @schema AS VARCHAR(256) = 'dbo.'
--User can also use SELECT SCHEMA_NAME() to get the default schema name




DECLARE @ID INT


declare @SQL nvarchar(max) = 'EXEC ' + @schema +'spSelectCaseBookingDetails @BookingID = '  + CAST(@ID AS NVARCHAR(10))

No need to cast @ID if it is nvarchar or varchar

execute (@SQL)

What i sometimes do when i need a lot of tablenames ill just get them plus their schema from the INFORMATION_SCHEMA system table: value

select  TABLE_SCHEMA + '.' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME in
(*select your table names*)

Try setuser. Example

declare @schema nvarchar (256)
set @schema=(
select top 1 TABLE_SCHEMA
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME='MyTable'
)
if @schema<>'dbo' setuser @schema

SETUSER could work, having a user, even an orphaned user in the DB with the default schema needed. But SETUSER is on the legacy not supported for ever list. So a similar alternative would be to setup an application role with the needed default schema, as long as no cross DB access is needed, this should work like a treat.

Very old question, but since google led me here I'll add a solution that I found useful:

Step 1. Create a user for each schema you need to be able to use. E.g. "user_myschema"

Step 2. Use EXECUTE AS to execute the SQL statements as the required schema user.

Step 3. Use REVERT to switch back to the original user.

Example: Let's say you have a table "mytable" present in schema "otherschema", which is not your default schema. Running "SELECT * FROM mytable" won't work.

Create a user named "user_otherschema" and set that user's default schema to be "otherschema".

Now you can run this script to interact with the table:

EXECUTE AS USER = 'user_otherschema';
SELECT * FROM mytable
REVERT

The revert statements resets current user, so you are yourself again.

Link to EXECUTE AS documentation: https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-2017

For Oracle, please use this simple command:

ALTER SESSION SET current_schema = your-schema-without-quotes;