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

Question

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

Answer 1

You can use the INFORMATION_SCHEMA. Here, TABLES view is used to get the list of tables. You can Generate the Drop scripts in select statement and drop it using Dynamic SQL like this:

Query:

DECLARE @sql NVARCHAR(max)=''SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '

FROM   INFORMATION_SCHEMA.TABLES

WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

Sys.Tables Version

DECLARE @sql NVARCHAR(max)=''SELECT @sql += ' Drop table ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '

FROM   sys.tables t

       JOIN sys.schemas s

         ON t.[schema_id] = s.[schema_id]

WHERE  t.type = 'U'

Exec sp_executesql @sql

Note: If you have any foreign keys defined between tables then first run the below query to disable all foreign keys present in your database.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

You can master these queries and become proficient in SQL queries by enrolling in an industry-recognized SQL course.