为 sql 查询设置默认架构
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4942023/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
set default schema for a sql query
提问by Chev
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]
采纳答案by Sem Vanmeenen
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 2005 开始，您可以使用 ALTER USER 语句设置用户的默认架构。不幸的是，这意味着您将永久更改它，因此如果您需要在模式之间切换，则每次执行存储过程或批处理语句时都需要设置它。或者，您可以使用此处描述的技术。
If you are using sql server 2000 or older this pageexplains 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 server 2000 或更早版本，此页面说明用户和架构是等效的。如果您没有在表名前面加上 schema\user，sql server 将首先查看当前用户拥有的表，然后查看 dbo 拥有的表来解析表名。似乎对于所有其他表，您必须预先添加架构\用户。
回答by mpontillo
I do not believe there is a "per query" way to do this. (You can use the usekeyword to specify the database- not the schema - but that's technically a separate query as you have to issue the gocommand afterward.)
我不相信有一种“每个查询”的方式来做到这一点。（您可以使用use关键字来指定数据库- 而不是模式 - 但从技术上讲，这是一个单独的查询，因为您必须在go之后发出命令。）
Remember, in SQL server fully qualified table names are in the format:
请记住，在 SQL Server 中，完全限定的表名采用以下格式：
[database].[schema].[table]
[数据库].[架构].[表]
In SQL Server Management Studio you can configure all the defaults you're asking about.
在 SQL Server Management Studio 中，您可以配置您询问的所有默认值。
You can set up the default
databaseon a per-user basis (or in your connection string):Security > Logins > (right click) user > Properties > General
You can set up the default
schemaon a per-user basis (but I do not believe you can configure it in your connection string, although if you usedbothat is always the default):Security > Logins > (right click) user > Properties > User Mapping > Default Schema
您可以
database基于每个用户（或在您的连接字符串中）设置默认值：安全 > 登录 >（右键单击）用户 > 属性 > 常规
您可以
schema基于每个用户设置默认值（但我不相信您可以在连接字符串中配置它，尽管如果您使用dbo它始终是默认值）：安全 > 登录 >（右键单击）用户 > 属性 > 用户映射 > 默认架构
In short, if you use dbofor your schema, you'll likely have the least amount of headaches.
简而言之，如果您使用dbo架构，您可能会遇到最少的麻烦。
回答by Culme
Very old question, but since google led me here I'll add a solution that I found useful:
很老的问题，但由于谷歌把我带到这里，我将添加一个我认为有用的解决方案：
Step 1. Create a user for each schema you need to be able to use. E.g. "user_myschema"
步骤 1. 为您需要能够使用的每个模式创建一个用户。例如“user_myschema”
Step 2. Use EXECUTE AS to execute the SQL statements as the required schema user.
步骤 2. 使用 EXECUTE AS 作为所需的架构用户执行 SQL 语句。
Step 3. Use REVERT to switch back to the original user.
步骤 3. 使用 REVERT 切换回原始用户。
Example: Let's say you have a table "mytable" present in schema "otherschema", which is not your default schema. Running "SELECT * FROM mytable" won't work.
示例：假设您在架构“otherschema”中存在一个表“mytable”，这不是您的默认架构。运行“SELECT * FROM mytable”将不起作用。
Create a user named "user_otherschema" and set that user's default schema to be "otherschema".
创建一个名为“user_otherschema”的用户并将该用户的默认架构设置为“otherschema”。
Now you can run this script to interact with the table:
现在您可以运行此脚本与表进行交互：
EXECUTE AS USER = 'user_otherschema';
SELECT * FROM mytable
REVERT
The revert statements resets current user, so you are yourself again.
revert 语句重置当前用户，所以你又是你自己。
Link to EXECUTE AS documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-2017
EXECUTE AS 文档链接：https: //docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view =sql-server- 2017
回答by olafk
What i sometimes do when i need a lot of tablenames ill just get them plus their schema from the INFORMATION_SCHEMA system table: value
当我需要很多表名时，我有时会做什么，只是从 INFORMATION_SCHEMA 系统表中获取它们加上它们的模式：值
select TABLE_SCHEMA + '.' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME in
(*select your table names*)
回答by Moogaloo
SETUSER could work, having a user, even an orphaned user in the DB with the default schema needed. But SETUSER is on the legacy not supported for ever list. So a similar alternative would be to setup an application role with the needed default schema, as long as no cross DB access is needed, this should work like a treat.
SETUSER 可以工作，在需要默认架构的数据库中拥有一个用户，甚至是一个孤立的用户。但是 SETUSER 位于永远不支持的遗留列表中。因此，类似的替代方法是使用所需的默认架构设置应用程序角色，只要不需要跨数据库访问，这应该像一种享受。
回答by John Merager
Try setuser. Example
尝试设置用户。例子
declare @schema nvarchar (256)
set @schema=(
select top 1 TABLE_SCHEMA
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME='MyTable'
)
if @schema<>'dbo' setuser @schema
回答by Atul Chaudhary
Another way of adding schema dynamically or if you want to change it to something else
动态添加模式的另一种方法，或者如果您想将其更改为其他内容
DECLARE @schema AS VARCHAR(256) = 'dbo.'
--User can also use SELECT SCHEMA_NAME() to get the default schema name
DECLARE @ID INT
declare @SQL nvarchar(max) = 'EXEC ' + @schema +'spSelectCaseBookingDetails @BookingID = ' + CAST(@ID AS NVARCHAR(10))
No need to cast @ID if it is nvarchar or varchar
如果是 nvarchar 或 varchar，则无需强制转换 @ID
execute (@SQL)
执行（@SQL）
