How to drop all tables in an SQL Server Schema

If you want to drop (delete) all tables in a schema, e.g. to reload the DWH, proceed as follows:

1. run this script

   -- set schemaName and run this script. 
   -- it will print DROP scripts for all tables in the schema
   DECLARE @schemaName NVARCHAR(50) = 'test'
   DECLARE @SqlStatement NVARCHAR(MAX)
   SELECT @SqlStatement = COALESCE(@SqlStatement, N'') + N'DROP TABLE [' + @schemaName + '].' + QUOTENAME(TABLE_NAME) + N';' + CHAR(13)
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = @schemaName and TABLE_TYPE = 'BASE TABLE'
   PRINT @SqlStatement
   

2. It will print out DROP statements for all the tables

3. Copy them and run them, e.g. in SQL Azure Studio

Variation: delete all staging tables

DECLARE @schemaName NVARCHAR(50) = 'bc'
DECLARE @SqlStatement NVARCHAR(MAX)
SELECT @SqlStatement = COALESCE(@SqlStatement, N'') + N'DROP TABLE [' + @schemaName + '].' + QUOTENAME(TABLE_NAME) + N';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%_stg'
AND TABLE_SCHEMA = @schemaName and TABLE_TYPE = 'BASE TABLE'
PRINT @SqlStatement