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:
You can find out more about our free SDU Tools here: