“This tutorial shows you various methods and techniques for removing an existing schema from a database. We will cover the DROP SCHEMA statement and illustrate how to remove a schema using the SQL Server Management Studio.”

Let’s delve in.

SQL Server Create Schema

Before demonstrating how to delete a schema, let us create a sample schema for illustration purposes.

If you wish to learn more about how you can create a schema in SQL Server, check out our tutorial provided in the link below:

https://linuxhint.com/sql-server-create-schema

In SQL Server, we can use the CREATE SCHEMA statement to initialize a new schema in the current database.

The statement syntax is as shown below:

 
We start by specifying the schema name we wish to create in the CREATE SCHEMA section.

The next part is to specify the owner of the schema we are creating as defined in the AUTHORIZATION section.

NOTE:  The operations of the CREATE SCHEMA statement are entirely atomic. Hence, the entire statement will fail if errors occur in the execution hierarchy.

SQL Server Create Schema – Transact-SQL

The following example shows how to create a simple schema using the CREATE SCHEMA statement.

 
Depending on the tool you are using to interact with the SQL Server, the command above may require you to use the GO command, which allows SQL Server to run all the commands before the GO command.

NOTE: The create schema command will create the schema in the currently selected database. In our example, we are using the local_db database.

SQL Server Drop Schema

We use the DROP SCHEMA statement to remove an existing schema from a SQL Server database. The syntax is as expressed below:

 
We start by calling the DROP SCHEMA keywords followed by the schema name we wish to remove. It is good to ensure that the schema you want to remove does not contain any objects. If the schema is not empty, the drop statement will fail with an error.

The IF EXISTS clause allows us to remove the schema conditionally. Therefore, the command will not return an error if a schema with the specified name does not exist.

Example

The following query shows how to use the DROP SCHEMA statement to remove the local_db_schema.

Example 2

As stated, the DROP SCHEMA clause will fail if the target schema contains any objects. Take the screenshot below:

As we can see from the image above, the local_db_schema contains a table object called databases. If we attempt to remove the schema without dropping the table first, the query will return an error as shown:

 
Resulting error:

 
Therefore, it is necessary to ensure that the schema is clean before dropping it.

Method 2 – SQL Server Drop Schema (SSMS)

We can also drop an existing schema using the SQL Server Management Studio. But first, open the Object Explorer and Locate the database on which the target schema resides.

Second, Expand to Security -> Schemas and locate the schema you wish to remove.

Right-Click the schema and choose the delete option.

SSMS should delete the schema from the database if the schema does not contain any objects.

Expiry

As promised, using this tutorial, you learned how to use the DROP SCHEMA statement to remove an existing schema from a database.