set default schema for a sql query

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try setuser  Example  declare  schema nvarchar  256  set  schema     select top 1 TABLE SCHEMA   from INFORMATION SCHEMA TABLES   where TABLE NAME  MyTable    if  schema lt  gt  dbo  setuser  schema

User · Answer

For Oracle  please use this simple command  ALTER SESSION SET current schema   your-schema-without-quotes

[sql] set default schema for a sql query

Examples related to sql

Examples related to sql-server

Examples related to schema