What's the difference between a Database and a Schema in SQL Server? Both are the containers of tables and data.
If a Schema is deleted, then are all the tables contained in that schema also deleted automatically or are they deleted when the Database is deleted?
A database is the main container, it contains the data and log files, and all the schemas within it. You always back up a database, it is a discrete unit on its own.
Schemas are like folders within a database, and are mainly used to group logical objects together, which leads to ease of setting permissions by schema.
drop schema test1
Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'test1' because it is being referenced by object 'copyme'.
You cannot drop a schema when it is in use. You have to first remove all objects from the schema.
Related reading: