SQL Server架构和默认架构

I have a schema define in my database. Except now everytime I do a sql statement I have to provide the schema ... SELECT * FROM [myschema].table

我的数据库中有一个架构定义。除了现在每次我做一个sql语句，我必须提供模式... SELECT * FROM [myschema] .table

I set the default schema for my user using management studio and also ran the ALTER USER myUser WITH DEFAULT_SCHEMA [myschema] and I still get the invalid object 'table' when writing a query without the schema (SELECT * FROM table)

我使用管理工作室为我的用户设置默认架构，并运行ALTER USER myUser WITH DEFAULT_SCHEMA [myschema]，在编写没有架构的查询时，我仍然得到无效对象'table'（SELECT * FROM table）

Is there a way to write SELECT * FROM table without having to specify the schema name all the time?

有没有办法编写SELECT * FROM表而不必一直指定模式名称？

It's on SQL 2005 using SQL Management Studio.

它是在SQL 2005上使用SQL Management Studio。

3 个解决方案

#1

Is the user an SA, if so it will not work, according to the documentation SA users are always defaulted to the dbo schema.

用户是SA，如果是这样，它将无法工作，根据文档SA用户总是默认为dbo架构。

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

如果用户是sysadmin固定服务器角色的成员，则忽略DEFAULT_SCHEMA的值。 sysadmin固定服务器角色的所有成员都具有dbo的默认架构。

#2

Couple of options:

几种选择：

Is your user listed under Security > Users (in SSMS)? Check the Properties (right click the name), and see if the Default schema is set in the context of the database, rather than the instance (which is what ALTER USER is setting).
您的用户是否列在安全>用户（SSMS）下？检查属性（右键单击名称），查看是否在数据库的上下文中设置了默认模式，而不是实例（这是ALTER USER设置的实例）。
Create a synonym for the table you want to reference:

为要引用的表创建同义词：
```
CREATE SYNONYM table_name  
   FOR [your_db].[your_schema].table_name
```
...which will affect everyone who doesn't use at least two name notation, in the context of that database. Read more about it here. But it is associated ultimately to a schema.

...这将影响在该数据库的上下文中不使用至少两个名称表示法的每个人。在这里阅读更多相关信息。但它最终与模式相关联。
Check that the database selected in the "Available Databases" drop down (upper left, to the left of the Execute button) is correct.

检查在“可用数据库”下拉列表中选择的数据库（“执行”按钮左上角）是否正确。
Use three name notation when specifying table (and view) references:

指定表（和视图）引用时使用三个名称表示法：
```
SELECT * 
  FROM [your_db].[your_schema].table_name
```

#3

If you do not want to use "full qualified" SQl names, then you need to avoid creating your tables using any account or role that's not using the "dbo" default schema assigned. Why do you need to change the default schema on the user if you don't plan on using it?

如果您不想使用“完全限定”的SQl名称，则需要避免使用未使用分配的“dbo”默认架构的任何帐户或角色创建表。如果您不打算使用它，为什么还需要更改用户的默认架构？

#1