[sql] set default schema for a sql query

Is there a way to set the schema for a query so that in the rest of the query I can refer to tables just by their name without prepending them with a schema name?

For instance, I would like to do something like this:

Use [schemaName]
select * from [tableName]

as opposed to this:

select * from [schemaName].[tableName]

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.

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

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)

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://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-2017

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.

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*)

For Oracle, please use this simple command:

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