I was trying to create a table in a schema, but received the following error message: Msg 2760, Level 16, State 1, Line 1
Read on to find out how I solved this problem.
Read more: Error Msg 2760, Level 16 When trying to create a table in a schemaWhy create a schema? A schema in SQL Server is a way to organize database objects such as tables, views, and stored procedures into logical groups. By creating separate schemas for different categories of information, we can easily manage and access the data we need, improve security, and simplify database maintenance.
Those are all good points, so I want to create a new table in a schema called HR. I get the following error that the HR schema does not exist. It is correct, I need to create the HR schema first before creating objects in it.
To create the schema, all you have to do is use the CREATE SCHEMA command.
Here is how I created the HR schema.
CREATE SCHEMA [HR]
After the schema is created, the table can now be created in the HR schema.
If I look in SSMS, I can see that the new table is created within the HR schema that just got created.
Below I am trying to drop the HR schema. It errors off with a Msg 3729, Level 16, State 1 error. It is telling us that there is an object that exists and it will not let you drop it until the objects are removed from the schema.
First drop the table.
Now I can successfully drop the schema.
To read more about schemas, I encourage you to follow this link to the documentation. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-ver16