SCHEMA_NAME() returns NULL

Question

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://learn.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.

Answer 1

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]

Answer 2

I was also facing the same issue with Schema_name() return null value but sys.database_principal showing default schema as my user name,Then I noticed that schema(username) was deleted by DBA team. When they recreate the same schema. My issue got resolved. You can check the list of schema in database using sys.schemas.