THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

USE Schema

Just to float an idea,


What if the Use command could also specify a default schema for that session? It wouldn't be as permanent as setting the user's default schema.


USE Adventureworks.Production


would change database and set the default schema to Production for that session.




Published Thursday, January 3, 2008 4:47 PM by Paul Nielsen



Greg Linwood said:

Sounds like a great idea to me Paul. If you create a connect I'll vote for sure..

January 3, 2008 8:24 PM

Chuck Boyce said:


I definitely like it...

January 3, 2008 9:44 PM

Peter W. DeBetta said:

I like it, but I think the syntax would need to be different, perhaps something like

USE AdventureWorks;

USE SCHEMA Production;

or something similar, such that USE SCHEMA is itself an independent statement, such that if you simply want to change the temporary schema, you could then say


and since you may want to switch back to the user's default schema, you would need a special syntax to revert to that, perhaps

--Requires the semicolon


or something to that effect.

January 3, 2008 10:11 PM

Peter W. DeBetta said:

Having a separate statement also allows for security to be handled more cleanly. If UserX had no permissions to BlahDB, then if UserX executed the following:


It would throw an exception:

Msg 916, Level 14, State 1, Line 1

The server principal "UserX" is not able to access the database "BlahDB" under the current security context.

Using the same concept, if UserX had no effective permissions to the YadaSchema schema, then executing this statement:

USE SCHEMA YadaSchema;

would also throw an exception, perhaps something like:

Msg 916, Level 14, State 1, Line 1

The server principal "UserX" is not able to access the schema "YadaSchema" under the current security context.

January 3, 2008 10:24 PM

Adam Machanic said:

Interesting, but I have to admit that I don't see a reason for it unless you could use it in a stored procedure (then, perhaps, it would be called USING -- similar to VB's construct of the similar name).

January 3, 2008 10:35 PM

Peter W. DeBetta said:

Adam: Scary how you and I thought alike about this one - I had originally had USING instead of USE SCHEMA and changed it last minute.

January 3, 2008 11:05 PM

Paul Nielsen said:

I could support any of these great suggestions. My beef is that the only two ways to address objects within schemas is to either type out the 2-part name or change the user's default schema. I'd prefer some way to set the default schema for a session.

I see the wisdom in a command separate from use. Although both a separate command AND a use database.schema would be ok.

I can see both sides of the USe Schema vs. Using

Use Schema seems more like T-SQL Use

Using would be different from Use with only one word.

If I had to vote I'd go with Use Schema

January 3, 2008 11:28 PM

David Markle said:


Can't we get TRIM() first?  ;-)

I think the limit is one addition to the language every 3-5 years.  :)  We're only now about to get DATE and TIME...

January 4, 2008 1:36 AM

Paul Nielsen said:

David, You are so right. I'd pick full support for the OVER caluse before Using or Trim()

January 4, 2008 1:44 AM

fibrock said:

How about a libary list of schemas to use in a particlular order for a session or just for any time.  Example: if schema1 and schema2 have the same table userX would always look to schema1 first for an object and schema2 if it didn't find it in schema1.

January 4, 2008 8:25 AM

Nick said:

I got here looking for a search on how to USE a specific schema.  Seems to me this is a no brainer, and should have been there from the start for no other reason than to help prevent this kind of OOPS!






(1 rows affected)





And 2 minutes later, phone rings...

June 17, 2008 2:14 PM

Axel Dahmen said:

Great idea. I came here by Goggling for just the solution you've proposed.

What about starting a Connect issue now?

September 23, 2008 10:23 AM

Josh Bixby said:

I agree, great idea.  Much to my surprise, I checked Microsoft Connect and no one has yet to submit this specific idea.  So, I submitted a feedback suggestion.

If you are reading this now and like the idea, I encourage you to vote for the idea.   The more people that vote/rate the idea, the better chance we have of seeing this sometime in the near future.

January 20, 2009 9:11 AM

Jonatas said:

Yes! Like PostgreSQl and the "SET search_path TO [SCHEMA NAME]" command.

February 9, 2009 7:43 AM

David said:

Great idea and I need it badly.

I expected this to be supported already and was searching to find the tsql but... found this article.

August 12, 2009 8:42 PM
New Comments to this post are disabled

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog



news item test
Privacy Statement