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.
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.SELECT 'Drop Table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS DropTableQuery FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Once you run above statement, Drop statements will be shown, copy them and run in new query.
Video Demo : How to generate drop table statement for all the tables in SQL Server Database