SQL: Finding the current default schema in a SQL Server session

Each SQL Server user has a default schema. This the used in several ways. If I execute code like this:

CREATE TABLE Blah
(
...
);

I haven’t specified where that table will be created. People seem to assume it will end up in the dbo schema i.e. the table will be dbo.Blah but that’s not the case. It depends upon the user’s default schema. If that’s Sales, then the table Sales.Blah would be created instead.

Similarly, if I execute code like this:

SELECT BlahID, BlahName FROM Blah;

Again, I haven’t said which schema the table Blah is in. So, SQL Server will first check my default schema, and if that’s Sales, it will first look for a table called Sales.Blah.

The recommendation is to always use two-part names for all objects that live in a schema.

Default Schema

One of my MVP friends recently asked how you can find the name of the current default schema.

Now you can find a user’s default schema by querying the sys.database_principals system view. It has a column default_schema_name that’ll work.

However, what if the user connects via group membership?

How can you tell the name of the current default schema?

The answer is to do this:

SELECT SCHEMA_NAME();

The SCHEMA_NAME() function will return the name of a schema if you pass it the schema_id but if you don’t pass anything, it will return the name of the current default schema.

2019-12-03