I am wondering if there is a way to change the schema that I am working in while inside Management Studio. For instance I may have a default schema of dbo. But there are times I may want to query objects in say the accounting schema. It would be nice if I could issue a command and make it so I no longer must include the accounting before tables and views. But the next time I go in, I will be back to default of dbo.
A:
You should always prefix your objects with a schema. In the AdventurWorks database you will notice extensive use of schemas:
Select ... From Person.Contact
Select ... From Person.StateProvince
In each query, you prefix the object with the schema and separate them with a period. (<schema>.<object>). It requires more work on the part of the system to figure out which object you want when you omit the schema. Even if all objects you reference are dbo, you should include dbo in all your object references.
Now, all that said, you can change your default schema using ALTER USER:
ALTER USER userName WITH DEFAULT_SCHEMA = schemaName
Even so, I would highly recommend you always include the schema when referencing any object.
Thomas
2010-04-17 00:43:23
I know I can always use the schema name, and that I can change my default schema. What I am looking for is a quick way to change that default for just the particular connection.I understand the whys of fully qualified names, but as an honest person there are times I just want to rapidly prototype something without the extra overhead of schema info.
Kirk
2010-04-18 13:18:45
@Kirk - There is no means to set a default schema per connection. The only way would be to use the Alter statement which would change it for your user. You could of course, simply put an alter statement at the top and another at the bottom of your script to reset it.
Thomas
2010-04-18 15:10:55