Open In App

DROP SCHEMA in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

The DROP SCHEMA statement could be used to delete a schema from a database. SQL Server have some built-in schema, for example : dbo, guest, sys, and INFORMATION_SCHEMA which cannot be deleted.

Syntax :

DROP SCHEMA [IF EXISTS] schema_name;

Note : Delete all objects from the schema before dropping the schema. If the schema have any object in it, the output will be an error.

Example :
Let us create a new schema named geeksh (https://www.geeksforgeeks.org/create-schema-in-sql-server/);

CREATE SCHEMA geeksh;
GO

Now create a table named geektab inside the geeksh schema :

CREATE TABLE geeksh.geektab
(id INT PRIMARY KEY,
date DATE NOT NULL,
city varchar(200) NOT NULL);

Drop the schema geeksh :

DROP SCHEMA geeksh;
SQL Server will throw similar error as the schema is not empty.

Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'geeksh' because it is being referenced by object 'geektab'.

Let us drop the table geeksh.geektab :

DROP TABLE geeksh.geektab;

Again run the DROP SCHEMA again to drop the geeksh schema :

DROP SCHEMA IF EXISTS geeksh;

Now, the geeksh schema has been deleted from the database.


Last Updated : 04 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads