MS SQL Server Interview Question:

How To Drop an Existing Schema in MS SQL Server?

Submitted by: Administrator

If you want to delete a schema, you need to move all objects out of that schema, then use the "DROP SCHEMA" statement to delete the schema. The tutorial exercise below shows you how to drop schema "ggl":

-- Login with "sa"

USE GlobalGuideLineDatabase;
GO

-- Drop failed because schema is not empty
DROP SCHEMA ggl;
GO
Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'ggl' because it is being referenced
by object 'DF__ggl_links__creat__4316F928'.

-- Move one table out
ALTER SCHEMA dbo TRANSFER ggl.ggl_links;
GO

-- Delete one table
DROP TABLE ggl.test;
GO

-- Dropped ok
DROP SCHEMA ggl;
GO
Command(s) completed successfully.
Submitted by: Administrator