set default schema for a sql query

sql sql-server schema
Chev picture Chev · Feb 9, 2011 · Viewed 148.7k times · Source

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]

Answer

Sem Vanmeenen picture Sem Vanmeenen · Feb 9, 2011

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.

Related questions

Change Schema Name Of Table In SQL

I want to change schema name of table Employees in Database. In the current table Employees database schema name is dbo I want to change it to exe. How can I do it ? Example: FROM dbo.Employees TO exe.Employees …

Read More
How do I change db schema to dbo

I imported a bunch of tables from an old sql server (2000) to my 2008 database. All the imported tables are prefixed with my username, for example: jonathan.MovieData. In the table properties it lists jonathan as the db schema. When I …

Read More
Rename SQL Server Schema

How can I rename a schema using SQL Server?

Read More