为 sql 查询设置默认架构 [英] set default schema for a sql query

问题描述

有没有办法为查询设置架构，以便在查询的其余部分中，我可以仅通过表的名称来引用表，而无需在它们前面加上架构名称?

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]

与此相反:

select * from [schemaName].[tableName]

推荐答案

一个快速的谷歌将我指向 此页面.它解释了从 sql server 2005 开始，您可以使用 ALTER USER 语句设置用户的默认架构.不幸的是，这意味着您将永久更改它，因此如果您需要在模式之间切换，则每次执行存储过程或一批语句时都需要设置它.或者，您可以使用描述的技术 这里.

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.

如果您使用的是 sql server 2000 或更早版本 此页面 说明那么用户和模式是等效的.如果您没有在表名前面加上 schema\user，sql server 将首先查看当前用户拥有的表，然后查看 dbo 拥有的表来解析表名.似乎对于所有其他表，您必须在 schema\user 前面加上.

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.

这篇关于为 sql 查询设置默认架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！