USE Schema

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

hmmmm...intrigued...

I definitely like it...

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.

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.

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).

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.

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...

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.

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...

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

What about starting a Connect issue now?

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.

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

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.