• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Basics / SQL Server DROP SCHEMA

SQL Server DROP SCHEMA

Learning Objective

In this tutorial, we will teach you how to use DROP SCHEMA in SQL Server to remove an existing schema from the SQL Server database.

SQL Server DROP SCHEMA Introduction

SQL Server DROP SCHEMA allows to drop an existing schema from the SQL Server database.

SQL Server DROP SCHEMA Syntax

The follwing shows the syntax of the SQL Server DROP SCHEMA statement.

DROP SCHEMA [IF EXISTS] schema_name;

In this syntax,

  • DROP SCHEMA – keyword to drop an existing schema from the SQL Server database.
  • IF EXISTS – Optional. This ensures that remove the schema only if the schema exists in SQL Server instance. Attempting to drop a non-existing schema SQL Server will give an error.
  • schema_name – Name of the schema which you want to drop.

SQL Server DROP SCHEMA Example

To understand SQL Server DROP SCHEMA statement,

First, create a schema named college.

CREATE SCHEMA college;

Next, create a table named students inside the college schema.

CREATE TABLE college.students
(
   roll_no INT PRIMARY KEY IDENTITY,
   first_name nvarchar(50) NOT NULL,
   last_name nvarchar(50) NOT NULL,
   phone_no nvarchar(20),
   address nvarchar(255) NOT NULL
);

Now, drop the college schema.

DROP SCHEMA college;

SQL Server will give an error beause the schema is not empty. So, you can drop a schema only if the schema does not have any children in it.

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

Next, remove the students table from college schema.

DROP table college.students;

Finally, issue the DROP SCHEMA again to drop the schema.

DROP SCHEMA college;

Now, you will observe that college schema has been removed from the database.

Summary

In this tutorial, you have learned how to use DROP SCHEMA statement in SQL Server to remove an existing schema from SQL Server database.

Was this tutorial helpful?
YesNo
« Previous: SQL Server ALTER SCHEMA
SQL Server CREATE TABLE :Next »

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.