SDU Tools: Empty Schema

Occasionally I run into a need to clear out all objects in a schema in a SQL Server database. Sometimes it’s because I want to remove the schema itself. It’s often painful to do as I have to find all the types of T-SQL objects, and then often have to remove them in a particular order.

So I added a tool to my SDU Tools collection to do just that.

It’s a simple tool and easy to use. It takes two parameters:

@DatabaseName (sysname data type) is the name of the database containing the schema

@SchemaName (sysname data type) is the name of the schema to empty. This cannot be SDU_Tools (use our script to remove the tools instead), and cannot be sys (that could be nasty).

WARNING: Obviously this is a potentially destructive tool.

Example of use:

EXEC SDU_Tools.EmptySchema @DatabaseName = N’WideWorldImporters’, @SchemaName = N’XYZABC';

You can see it in use here: