找出会话的默认SQL Server架构

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中工作。

3 个解决方案

#1

How about this.

这个怎么样。

SELECT SCHEMA_NAME()

SELECT SCHEMA_NAME（）

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（）

#2

How about using DATABASE_PRINCIPAL_ID to get the db principal of the current user?

如何使用DATABASE_PRINCIPAL_ID获取当前用户的db主体？

SELECT name, default_schema_name 
FROM sys.database_principals 
WHERE type = 'S' and name = USER_NAME(DATABASE_PRINCIPAL_ID())

#3

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.

我希望这有帮助。

#1