Schema are like namespace which enables us to group database objects into logical groups for ease of use, security and manageability. It is a Best Practice to use Schema Names in queries. For example, if we are trying to retrieve the data from the “Address” table from the “SalesLT” Schema, we should write the query in the following way:
SELECT * FROM SalesLT.Address
If a Schema has some table in it, then we cannot drop the Schema until we remove all the objects out of it.
I have created a Schema “TestUser_FirstSchema” for this purpose. I am creating the sample table in the Schema.
CREATE TABLE TestUser_FirstSchema.TestTable
Now if I want to DROP the Schema, then I will get the following error:
Msg 3729, Level 16, State 1, Line 1
Cannot drop schema ‘TestUser_FirstSchema’ because it is being referenced by object ‘TestTable’.
We cannot even DROP any Login or User that owns a Schema. If we try to do that then we will get the following error:
Msg 15138, Level 16, State 1, Line 2
The database principal owns a schema in the database, and cannot be dropped.
Now to drop a Schema, we have to first TRANSFER or DROP all the existing objects in the current Schema. The following script will transfer the “TestTable” object from the “TestUser_FirstSchema” to “TestUser_SecondSchema”.
ALTER SCHEMA TestUser_SecondSchema TRANSFER TestUser_FirstSchema.TestTable.
So we can see that the Schema of the “TestTable” object has been changed. Now since TestUser_FirstSchema is empty now, we can go ahead and drop this Schema.
DROP SCHEMA TestUser_FirstSchema
Let me know if you have any doubts regarding this.