找出会话的默认 SQL Server 架构
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5513053/
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
Find out default SQL Server schema for session
提问by Jon Egerton
I have a requirement to know what the current default schema is in a SQL script that is doing some DDL. I don't need to set the schema, but I do need to get a reference to it (name or ID) into a variable. The script may be running as a windows login, so the following isn't sufficient:
我需要知道正在执行某些 DDL 的 SQL 脚本中当前的默认架构是什么。我不需要设置架构，但我确实需要将它的引用（名称或 ID）放入一个变量中。该脚本可能作为 Windows 登录名运行，因此以下内容是不够的：
SELECT name, default_schema_name
FROM sys.database_principals
WHERE type = 'S' and name = SYSTEM_USER --SYSTEM User won't be named as a principal
I've thought of doing it by creating a randomly named object in the current schema, and then looking at its details in the information_schema, but does anyone have a tidier way?
我想通过在当前模式中创建一个随机命名的对象，然后在 information_schema 中查看它的详细信息来完成它，但是有人有更整洁的方法吗？
I'm working in SQL Server 2005.
我在 SQL Server 2005 中工作。
回答by Chris Diver
How about this.
这个怎么样。
SELECT SCHEMA_NAME()
SELECT SCHEMA_NAME()
http://msdn.microsoft.com/en-us/library/ms175068.aspx
http://msdn.microsoft.com/en-us/library/ms175068.aspx
SCHEMA_NAME will return the name of the default schema of the caller
SCHEMA_NAME 将返回调用者默认架构的名称
Alternatively SCHEMA_ID()
或者 SCHEMA_ID()
回答by Ed Harper
How about using DATABASE_PRINCIPAL_IDto get the db principal of the current user?
如何使用DATABASE_PRINCIPAL_ID获取当前用户的数据库主体？
SELECT name, default_schema_name
FROM sys.database_principals
WHERE type = 'S' and name = USER_NAME(DATABASE_PRINCIPAL_ID())
回答by Anand Thangappan
Here's what you can do to see more information about your current schema:
要查看有关当前架构的更多信息，您可以执行以下操作：
select sc.name as schemaname, sp.[name] as owner,
sp.default_database_name as [database] from sys.schemas sc
inner join sys.server_principals sp on sc.principal_id = sp.principal_id
select sc.name as schemaname, sp.[name] as owner,
sp.default_database_name as [database],sp.* from sys.schemas sc
inner join sys.server_principals sp on sc.principal_id = sp.principal_id
SELECT name, default_schema_name
FROM sys.database_principals WHERE type = 'S'
I hope this helps.
我希望这有帮助。
