Set default schema for SQL query

Question

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

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

Use [schemaName] select * from [tableName] 

in contrast to this:

 select * from [schemaName].[tableName] 

Answer 1

A quick google pointed me to this page . It explains that from SQL Server 2005, you can set the default schema for a user using the ALTER USER statement. Unfortunately, this means that you are constantly changing it, so if you need to switch between schemes, you will need to set it every time you execute a stored procedure or a batch of statements. Alternatively, you can use the technique described here .

If you are using SQL Server 2000 or later, this page explains that users and schemas are equivalent. If you did not add the table name using the \ user schema, the sql server will first consider the tables belonging to the current user, and then those belonging to dbo to resolve the table name. It seems that for all other tables you should add the \ user schema.

Answer 2

I do not believe that there is a "for every request" method. (You can use the use keyword to specify the database, not the schema, but this is technically a separate request, since you must subsequently issue the go command.)

Remember that on a SQL server, full table names have the format:

[base]. [Scheme]. [Table]

In SQL Server Management Studio, you can configure all the default settings that you are asking about.

• You can configure the default database for each user (or in the connection string):

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

• You can configure the default schema for each user (but I do not believe that you can configure it in your connection string, though if you use dbo , which is always the default):

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

In short, if you use dbo for your circuit, you are likely to have the least amount of headaches.

Answer 3

What I sometimes do when I need a lot of tablenames, I 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*) 

Answer 4

SETUSER can work with a user, even an orphaned user in the database with the necessary default scheme. But SETUSER's legacy is not supported forever. Thus, a similar alternative may be to install the application role with the necessary default scheme, if cross-access to the database is not required, this should work as a pleasure.

Answer 5

A very old question, but since Google brought me here, I will add a solution that I found useful:

Step 1. Create a user for each scheme that you need to use. For example. "User_myschema"

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

Step 3. Use REVERT to return to the original user.

Example: Suppose you have a table "mytable" present in the scheme "otherschema", which is not your default scheme. Running "SELECT * FROM mytable" will not work.

Create a user named "user_otherschema" and set thisschema to the default value of "otherschema".

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

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

Return statements reset the current user, so you become yourself again.

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

Answer 6

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 

Answer 7

Another way to add a scheme 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)