How to Generate Drop Table Statement for all the tables in a database - SQL Server / T-SQL Tutorial Part 48

Scenario:

You are working as SQL Server developer in Credit Card Company. You need to generate Drop Table statements for all the tables in Database TechBrothersIT that you are using as Staging for Data Loading process. How would you do that?

Solution:

There are multiple ways to generate Drop Statements for All the tables in a database. We can use Object Explorer Detail Windows, Click HERE to see the blog and video. But in this post, We are going to use simple Select Query to generate Drop Statements for all the tables in a database.


SELECT 'Drop Table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS DropTableQuery
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'


If you would like to filter the Tables by Schema, you can add Where Clause to Query or if you like to filter tables by name you can add Table_Name in Where clause.

Once you run above statement, Drop statements will be shown, copy them and run in new query.

How to Generate Drop Table Statements for all the tables in SQL Server Database - T-SQL Tutorial

Video Demo : How to generate drop table statement for all the tables in SQL Server Database