T-SQL Tutorial

How to get SQL Server schema id

A schema in SQL Server serves as a logical container for organizing database objects, including tables, views, and stored procedures. Each schema possesses a unique identifier, aptly named the schema ID. This ID serves as a reference point for addressing the schema in various SQL operations, such as granting permissions or filtering query results.

Two primary methods exist for retrieving the schema ID of a specified schema in SQL Server:

Leveraging the SCHEMA_ID() Function

The SCHEMA_ID() function, an integral part of SQL Server's repertoire, takes a schema name as input and promptly returns its corresponding schema ID. The syntax for employing the SCHEMA_ID() function is straightforward:

SCHEMA_ID ( [ schema_name ] )

Where schema_name represents the schema whose ID you seek. If no schema name is provided, the function defaults to returning the schema ID of the current user's default schema.



This query effectively extracts the schema ID associated with the 'dbo' schema.

Utilizing the sys.schemas System View

The sys.schemas system view encompasses a column named schema_id, which stores the schema ID for each schema within the database. By employing a SELECT statement to interrogate this view, you can readily obtain the schema ID of a specific schema. The syntax for querying sys.schemas is as follows:

SELECT schema_id
FROM sys.schemas
WHERE name = 'schema_name';

Where name represents the schema whose ID you desire.


SELECT schema_id
FROM sys.schemas
WHERE schema_name = 'dbo';

This query fetches the schema ID associated with the 'dbo' schema.

Both the SCHEMA_ID() function and the sys.schemas system view equip you with the capability to retrieve the schema ID of a given schema. The choice between these methods hinges on your specific requirements. For infrequent schema ID retrieval, the SCHEMA_ID() function offers a straightforward and concise approach. However, for frequent retrieval across multiple schemas, querying sys.schemas may prove more efficient.