DROP SCHEMA in SQL Server
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
Share your thoughts in the comments
Please Login to comment...