In SQL Server, you can use the SCHEMA_ID() function to return the ID of a given schema. More specifically, this function returns the schema ID associated with a schema name.
It’s like SCHEMA_NAME() except it returns the schema’s ID instead of the name (and it accepts the name parameter instead of ID).
If you don’t pass a schema name to the function, it returns the ID of the default schema of the caller.
Example 1 – Return Default Schema
Here’s an example that returns the ID of the default schema of the caller.
SELECT SCHEMA_ID() AS Result;
Result:
+----------+ | Result | |----------| | 1 | +----------+
This returns the ID of the default schema of the caller because I didn’t explicitly specify any other schema name.
Here it is again, along with the schema name.
SELECT SCHEMA_ID() AS [Schema ID], SCHEMA_NAME() AS [Schema Name];
Result:
+-------------+---------------+ | Schema ID | Schema Name | |-------------+---------------| | 1 | dbo | +-------------+---------------+
Example 2 – Specify a Different Schema
In this example I explicitly pass a schema name to the function.
SELECT SCHEMA_ID('Dimension') AS Result;
Result:
+----------+ | Result | |----------| | 6 | +----------+
This tells me that the schema called Dimension has an ID of 6.
Example 3 – Switching Databases
The previous example just happened to be run in a database that had a schema called Dimension. If I switch to a different database, I might get a different schema ID, or no ID at all.
Here’s an example of what I mean.
USE WideWorldImportersDW;
SELECT SCHEMA_ID('Dimension') AS Result;
USE Music;
SELECT SCHEMA_ID('Dimension') AS Result;
Result:
Changed database context to 'WideWorldImportersDW'. +----------+ | Result | |----------| | 6 | +----------+ (1 row affected) Changed database context to 'Music'. +----------+ | Result | |----------| | NULL | +----------+ (1 row affected)
The second result returns NULL because there’s no schema called Dimension in the Music database.
Example 4 – In a WHERE Clause
Using SCHEMA_ID() in a WHERE clause can be a handy way to filter the results by schema.
In SQL Server, various system views use a schema_id column to store the schema ID but not the schema name. Therefore you need to know the schema ID if you’re going to filter the results by schema. That’s where SCHEMA_ID() can be very useful. It saves you from having to do a join on the sys.schemas view just to work out the schema name.
Here’s an example of using SCHEMA_ID() in a WHERE clause.
USE WideWorldImportersDW;
SELECT
name,
type_desc
FROM sys.objects
WHERE schema_id = SCHEMA_ID('Dimension');
Result:
Changed database context to 'WideWorldImportersDW'. +----------------------------------------------------+------------------------+ | name | type_desc | |----------------------------------------------------+------------------------| | City | USER_TABLE | | PK_Dimension_City | PRIMARY_KEY_CONSTRAINT | | DF_Dimension_City_City_Key | DEFAULT_CONSTRAINT | | Customer | USER_TABLE | | PK_Dimension_Customer | PRIMARY_KEY_CONSTRAINT | | DF_Dimension_Customer_Customer_Key | DEFAULT_CONSTRAINT | | Date | USER_TABLE | | PK_Dimension_Date | PRIMARY_KEY_CONSTRAINT | | Employee | USER_TABLE | | PK_Dimension_Employee | PRIMARY_KEY_CONSTRAINT | | DF_Dimension_Employee_Employee_Key | DEFAULT_CONSTRAINT | | Payment Method | USER_TABLE | | PK_Dimension_Payment_Method | PRIMARY_KEY_CONSTRAINT | | DF_Dimension_Payment_Method_Payment_Method_Key | DEFAULT_CONSTRAINT | | Stock Item | USER_TABLE | | PK_Dimension_Stock_Item | PRIMARY_KEY_CONSTRAINT | | DF_Dimension_Stock_Item_Stock_Item_Key | DEFAULT_CONSTRAINT | | Supplier | USER_TABLE | | PK_Dimension_Supplier | PRIMARY_KEY_CONSTRAINT | | DF_Dimension_Supplier_Supplier_Key | DEFAULT_CONSTRAINT | | Transaction Type | USER_TABLE | | PK_Dimension_Transaction_Type | PRIMARY_KEY_CONSTRAINT | | DF_Dimension_Transaction_Type_Transaction_Type_Key | DEFAULT_CONSTRAINT | +----------------------------------------------------+------------------------+ (23 rows affected)