How to drop all tables from a database with one SQL query

Question

What if we don't want to type all tables' name to drop all of them. Is it all possible with writing one query?

Answer 1

DROP TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name } [ ,...n ] [ ; ] 

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }

Answer 2

To acquire a list of tables, use the INFORMATION SCHEMA.TABLES view. Using Dynamic SQL, generate Drop scripts in the select statement and drop them:

@sql NVARCHAR(max)=" DECLARE

SELECT @sql +='Drop table'+ QUOTENAME(TABLE SCHEMA) + '.'+ QUOTENAME(TABLE NAME) + '.'+ QUOTENAME(TABLE NAME) + '.'+ QUOTENAME(TABLE NAME) + ';'FROM INFORMATION SCHEMA.

TABLES IN WHICH THE TABLE TYPE IS 'BASE TABLE'

@sql Exec Sp executesql