In SQL Server, you can use the SCHEMA_NAME()
function to return the name of a particular schema. The way it works is that it returns the schema name associated with a schema ID.
If you don’t pass a schema ID to the function, it returns the name of the default schema of the caller.
Example 1 – Return Default Schema
Here’s an example that returns the name of the default schema of the caller.
SELECT SCHEMA_NAME() AS Result;
Result:
+----------+ | Result | |----------| | dbo | +----------+
This returns the name of the default schema of the caller because I didn’t explicitly specify another schema ID.
Example 2 – Specify a Different Schema
In this example I pass a schema ID to the function.
SELECT SCHEMA_NAME(7) AS Result;
Result:
+----------+ | Result | |----------| | Fact | +----------+
Example 3 – Switching Databases
The previous example just happened to be run in a database that had a schema with an ID of 7. If I switch to a different database, I might get a different schema name, or no name at all.
Here’s an example of what I mean.
USE WideWorldImportersDW; SELECT SCHEMA_NAME(1) AS [1], SCHEMA_NAME(2) AS [2], SCHEMA_NAME(3) AS [3], SCHEMA_NAME(4) AS [4], SCHEMA_NAME(5) AS [5], SCHEMA_NAME(6) AS [6], SCHEMA_NAME(7) AS [7], SCHEMA_NAME(8) AS [8]; USE Music; SELECT SCHEMA_NAME(1) AS [1], SCHEMA_NAME(2) AS [2], SCHEMA_NAME(3) AS [3], SCHEMA_NAME(4) AS [4], SCHEMA_NAME(5) AS [5], SCHEMA_NAME(6) AS [6], SCHEMA_NAME(7) AS [7], SCHEMA_NAME(8) AS [8];
Result:
Changed database context to 'WideWorldImportersDW'. +-----+-------+--------------------+-----+-------------+-----------+------+-------------+ | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |-----+-------+--------------------+-----+-------------+-----------+------+-------------| | dbo | guest | INFORMATION_SCHEMA | sys | Application | Dimension | Fact | Integration | +-----+-------+--------------------+-----+-------------+-----------+------+-------------+ (1 row affected) Changed database context to 'Music'. +-----+-------+--------------------+-----+------+------+------+------+ | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |-----+-------+--------------------+-----+------+------+------+------| | dbo | guest | INFORMATION_SCHEMA | sys | NULL | NULL | NULL | NULL | +-----+-------+--------------------+-----+------+------+------+------+ (1 row affected)
Four columns return NULL
on the Music database, because there’s no schema with that ID.
Example 4 – More Readable Query Results
Here’s an example of using SCHEMA_NAME()
to present the schema name instead of its ID when returning results from a system view.
SELECT schema_id, SCHEMA_NAME(schema_id) AS [Schema Name], name AS [Table Name] FROM sys.tables;
Result:
+-------------+---------------+-------------------------+ | schema_id | Schema Name | Table Name | |-------------+---------------+-------------------------| | 8 | Integration | ETL Cutoff | | 8 | Integration | Lineage | | 8 | Integration | Customer_Staging | | 8 | Integration | Employee_Staging | | 8 | Integration | Movement_Staging | | 8 | Integration | Order_Staging | | 8 | Integration | PaymentMethod_Staging | | 6 | Dimension | City | | 8 | Integration | Purchase_Staging | | 6 | Dimension | Customer | | 8 | Integration | Sale_Staging | | 8 | Integration | StockHolding_Staging | | 6 | Dimension | Date | | 8 | Integration | StockItem_Staging | | 6 | Dimension | Employee | | 8 | Integration | Supplier_Staging | | 6 | Dimension | Payment Method | | 8 | Integration | Transaction_Staging | | 8 | Integration | TransactionType_Staging | | 6 | Dimension | Stock Item | | 6 | Dimension | Supplier | | 6 | Dimension | Transaction Type | | 7 | Fact | Movement | | 7 | Fact | Order | | 7 | Fact | Purchase | | 7 | Fact | Sale | | 7 | Fact | Stock Holding | | 7 | Fact | Transaction | | 8 | Integration | City_Staging | +-------------+---------------+-------------------------+
The sys.tables
system view returns the schema ID but not its name. That’s not a problem though. The ID is sufficient, because we can use SCHEMA_NAME()
to display the schema’s name, based on that ID.
If we didn’t have the SCHEMA_NAME()
function, we’d need to do a join on the sys.schemas
system view just to get the schema’s name.
Example 5 – In a WHERE Clause
Here’s an example of using SCHEMA_NAME()
in a WHERE
clause.
USE WideWorldImportersDW; SELECT * FROM sys.schemas WHERE name = SCHEMA_NAME(7);
Result:
+--------+-------------+----------------+ | name | schema_id | principal_id | |--------+-------------+----------------| | Fact | 7 | 1 | +--------+-------------+----------------+
If you need to get a schema’s ID, use the SCHEMA_ID()
function.