THE SQL Server Blog Spot on the Web

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

Paul Nielsen

www.SQLServerBible.com

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.

 

thoughts?

 

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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:

hmmmm...intrigued...

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

USE SCHEMA Sales;

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

USE SCHEMA;

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:

USE BlahDB;

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:

LOL

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!

>SQLCMD -E

1>SELECT COUNT(*) FROM MY_TEST_SCHEMA.MY_DATA WHERE MY_COLUMN IS NULL

2>GO

----------

    13245

(1 rows affected)

1>DELETE FROM MY_DATA WHERE MY_COLUMN IS NULL

2>GO

EXIT

>

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.  

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=390528

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

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement