SCHEMA_NAME() 返回 NULL

我在 T-SQL 脚本中使用 SCHEMA_NAME() 函数.

它为我返回 NULL，但为我的同事返回默认架构.

SCHEMA_NAME() 的文档仅将 NULL 列为可能的值，同时还为 SCHEMA_NAME() 函数提供了一个参数.

以下是 SCHEMA_NAME() 的文档:https://docs.microsoft.com/en-us/sql/t-sql/functions/schema-name-transact-sql

在调试这个问题时我应该寻找什么?

DBA 已检查我的同事和我自己的权限是否相同.

我在不带任何参数的情况下调用该函数，因此文档中声明的在 schema_id 无效时返回 NULL 的情况不适用 - 至少没有明确说明.

DBA 确认我有一个默认架构，我使用以下选择来确认我有一个默认架构:

select * from sys.database_principles dp where dp.name = USER_NAME();

DBA 将我的默认架构更改为其他内容，然后将其更改回来以防后台设置了某些内容.

这发生在 SQL Studio 中以及当我使用 sqlcmd 在命令行上运行时.

我注意到当我运行 CREATE PROCEDURE myproc 过程是在我的默认架构中创建的，但是当我运行 execute myproc 我需要做 executemyschema.myproc.即:我必须指定架构.

我已通过在调用存储过程中硬编码架构名称来验证默认架构是否存在.

解决方案

当您在与 schema_id 不同的数据库上执行查询时，也会发生这种情况

例如

select sn = schema_name(schema_id) from [yourdbname].sys.all_objects

并且当前数据库不是 [yourdbname]

I am using the SCHEMA_NAME() function in a a T-SQL script.

It returns NULL for me, but the default schema for my colleague.

The documentation for SCHEMA_NAME() only lists NULL as a possible value when one also supplies an argument to the SCHEMA_NAME() function.

Here is the documentation for SCHEMA_NAME(): https://docs.microsoft.com/en-us/sql/t-sql/functions/schema-name-transact-sql

What should I look for when debugging this problem?

The DBA has checked that my permissions are same for my colleague and myself.

I am calling the function without any arguments so the documentation's stated case of returning NULL when schema_id is not valid does not apply -- at least not explicitly.

The DBA confirmed I have a default schema and I used the following select to confirm I have a default schema:

select * from sys.database_principles dp where dp.name = USER_NAME();

The DBA changed my default schema to something else, then changed it back in case there is something set in the background.

This happens in SQL Studio and when I run on the command line using sqlcmd.

I have noticed that when I run CREATE PROCEDURE myproc the procedure is created in my default schema, but when I run execute myproc I need to do execute myschema.myproc. That is: I must specify the schema.

I have verified that the default schema exists by hard coding the schema name in the call to the stored procedure.

解决方案

This can also happen when you are performing query on a different database than the schema_id

e.g.

select sn = schema_name(schema_id) from [yourdbname].sys.all_objects 

and the current database is not [yourdbname]