hi guys this is Mina, today we are going to talk about the drop statement
in sql server management studio. drop is used to either completely remove a
database, a schema or a table. so if you want to
delete a database completely from your sql
server you have to use the drop statement
and the syntax for dropping objects in SQL server is you type out
the word drop and if it's a database you type
database and then followed by the name of the database
and next if you want to drop a schema you type drop
schema and the name of the schema and lastly if it's a table
as you guess it's drop table, table name so let's move on to our management
studio to see how the syntax works
so guys this is our sql server management studio
and i've gone ahead to type out the syntax for dropping a table
a schema and a database. we're going to start with a table
and to do that we have to first make sure that we are in the right
database where the table is situated so we come to this drop down arrow and
select the right database the table we want to drop is in the
student record database so we come to student records
and go to tables and we want to drop the table
mega class student so what we have to do is type out the syntax
it's drop table and then the name of the table you can
type it out once i execute this command the table
has to be deleted completely from the system
however this syntax is not going to work because this table, the student table
is linked to another table in my database. Let's see that, i'll go to
database diagram and click on new database diagram
it's going to see the relationship between my
tables so i'll select all of them and add
okay as you can see here the table that we want to delete
is this student table but it is linked to another table which is the
courses table so we can't just go ahead and delete the
student table in order to be able to delete a student
table we have to either delete the relationship constraint
or delete the courses table before we can be able to remove the student table
i say delete the courses table because based on this relationship the courses
table is more like the daughter table and the student table is the
parent table. so you have to remove the daughter table
before you can be able to delete the student
table all right so let's go back to our syntax and see
this in action. i'm going to try to delete
this table and we should expect an error there, we cannot drop an object because
it is referenced by a foreign key constraint so like i said we have to
either remove the relationship which is the foreign
key constraint or delete the courses table before we
can be able to get down to delete the student table. i prefer to
remove the foreign key constraint so let's do that
we will go to our diagram and then we can right click on the
relationship and delete relationship yes okay so now there isn't any
relationship between these two tables we need to save this diagram
before we can be able to delete our student table so let's save it
okay yes and close now let's execute
our drop command see because there is no longer relationship
between the student table and the courses table
the command has been completed successfully
so remember if you want to drop any table make sure it is not related
to any other table so let's try the drop command for the
rest of the tables in our database i'm going to type drop table
and name of the table this time i want to delete courses table
so i would select that and execute courses table is gone and the last
in the database is table instructor and i'm going to
drop that as well so now we do not have any table in our database
i can click on table and go to refresh and now see there isn't any table at all
this is system table and that's not our table
so this is how to drop a table in sql server management studio
if you want to drop a schema in sequel server you have to make sure that
no table is referencing that schema and because all of the tables in our
database student record, have been deleted now we
can be able to delete this schema which is mega class
and to do that this is the syntax for dropping a schema so we can type
drop schema and then the name of the schema
and i'm going to show you where schema is located
in a database so we go to the appropriate database which is student
record we go to security and then
under schemas we click on the plus sign and this is the name of our schema
again no table is referencing our schema mega class
so we can execute this command and the schema is
also gone now we can go ahead and drop our database
as you guess is drop database and the name of the database
is student records. Before we can drop this database we have to make sure that
no one is connected to this database so for instance right now we are
connected to the student record database so we cannot
drop this command if i try to execute this it would give
us an error saying that the database is being
used, see, we cannot drop database because it is
currently in use so in order to be able to drop a
database we have to switch over to any of the database
in my sequel server and then we can execute
this command now it should work okay so command completed successfully so
right now we have been able to drop all our tables and drop our schema
and now we do not have any database called student record at
all so if we refresh our databases we shouldn't see
any database called student record. so this is how you
drop objects in sql server management studio
the syntax is drop and then if it's a database you type database and then the
name of the database you want to drop
make sure that that database is not in use make sure if it's a schema
no table is referencing that schema and also if it's a table make sure that it
is not being referenced as a foreign key in
another table there isn't any connection between the tables and
sequel server thank you so much for staying with me on
this video and see you in my next tutorial where i'm going to show you
how to truncate and delete tables in sql server have a good day
bye