All Transact-SQL statements that are used to remove a database object have the following general form:
DROP object_type object_name
Each CREATE object statement has the corresponding DROP object statement. The statement
DROP DATABASE databasel {, …}
removes one or more databases. This means that all traces of the database are removed from your database system.
One or more tables can be removed from a database with the following statement:
DROP TABLE table_name1 {, …}
All data, indices, and triggers belonging to the removed table are also dropped. (In contrast, all views that are defined using the dropped table are not removed.) Only the user with the corresponding privileges can remove a table.
In addition to DATABASE and TABLE, objects in the DROP statement can be, among others, the following:
- TYPE
- SYNONYM
- PROCEDURE
- INDEX
- VIEW
- TRIGGER
- SCHEMA
The statements DROP TYPE and DROP SYNONYM drop a type and a synonym, respectively. (Dropping the synonym does not have any influence on the underlying object.)
The rest of the statements are described in different chapters: DROP PROCEDURE in Chapter 8, DROP INDEX in Chapter 10, DROP VIEW in Chapter 11, DROP SCHEMA in Chapter 12, and DROP TRIGGER in Chapter 14.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.