Schema in DBMS

A schema is a collection of objects (tables, stored procedures, functions, etc) in a database, that are associated to one another. A schema helps to structure the database to group the logically associated objects. These schema are owned by an user, assigned at the time of creating the schema. Schema name and owners can be altered.

Default schema in any database is ‘dbo’ and the default owner is ‘dbo’. When you create a table (say ‘Bloggers’), the table name is dbo.Bloggers, when no shema is mentioned. Here ‘dbo’ is the schema name.

In AdventureWorks database, you find table names as below

     

The first part is the schema name and the second part is the actual table/stored procedure name. This way, the data has been more logically arranged within the database structure, as tables and stored procedures belonging to a particular department are grouped within the same schema like ‘HumanResources’, ‘Person’, ‘Production’ instead of stored just in the database.

User-defined functions must be called as schema_name.function_name(parameters if any)

 Syntax to create schema :

Create Schema schema_name

Example: create schema Bloggers

Syntax to create an object in a schema:

Create object schema_name.object_name (…..)

Example: create table Bloggers.Table1

         ( id int,

           name varchar(50)

         )

Happy SQLing!

Please post your comments (+ve or -ve, even neutral 😅) and questions and feel free share it to with your friends.

Cos sharing is caring..! 😎

Until next blog,

Vino BI Dev