Menu
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] 
424
To change the schema of a table by using SQL Server Management Studio, in Object Explorer, right-click on the table and then click Design. Press F4 to open the Properties window. In the Schema box, select a new schema. ALTER SCHEMA uses a schema level lock.
In that case, just use SQL Server Management Studio > Database > Security > Users > Properties and change the default schema there.
In the Database Administration view upper pane, select the database. Right-click the database and select Set default schema. The Set default schema dialog appears. Select the schema that you want to make the new default schema for the database from the drop-down list and click Ok to save the changes.
The dbo schema is the default schema of every database. By default, users created with the CREATE USER Transact-SQL command have dbo as their default schema. The dbo schema is owned by the dbo user account. Users who are assigned the dbo as default schema don't inherit the permissions of the dbo user account.
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.
67
I do not believe there is a "per query" way to do this. (You can use the use keyword to specify the database - not the schema - but that's technically a separate query as you have to issue the go command afterward.)
Remember, in SQL server fully qualified table names are in the format:
[database].[schema].[table]
In SQL Server Management Studio you can configure all the defaults you're asking about.
You can set up the default database on a per-user basis (or in your connection string):
Security > Logins > (right click) user > Properties > General
You can set up the default schema on a per-user basis (but I do not believe you can configure it in your connection string, although if you use dbo that is always the default):
Security > Logins > (right click) user > Properties > User Mapping > Default Schema
In short, if you use dbo for your schema, you'll likely have the least amount of headaches.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
