Drop all tables in schema (WHILE mode)

A simple script to drop all tables from the selected schema. You can adjust the metadata query simply and delete tables based on your own filter. Implement the CATCH block bases on your needs.

SET NOCOUNT ON

DECLARE @TableName NVARCHAR(300)
DECLARE @Stmt NVARCHAR(MAX)
DECLARE @TablesToBeDeleted TABLE (
	TableName NVARCHAR(300) NOT NULL PRIMARY KEY
)

INSERT INTO @TablesToBeDeleted
	SELECT 
		'[' + s.[name] + '].[' + t.name + ']'
	FROM sys.tables t
		INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
	WHERE s.name = 'stg'

WHILE EXISTS (SELECT * FROM @TablesToBeDeleted)
BEGIN
	
	SELECT 
		@TableName = TableName
	FROM @TablesToBeDeleted
	ORDER BY TableName

	PRINT @TableName

	SET @Stmt = 'DROP TABLE ' + @TableName

	BEGIN TRY

		EXECUTE sys.sp_executesql @Stmt, N''

	END TRY
	BEGIN CATCH

		;THROW

	END CATCH

	DELETE FROM @TablesToBeDeleted WHERE TableName = @TableName

END