Drop all Tables, Stored Procedures, Views and Triggers

Reset a development database quickly with ready-made cleanup scripts.

When rebuilding a development or test database from scratch, manually dropping every object one by one is tedious. SQL Server lets you generate and execute dynamic scripts that remove all tables, stored procedures, views, and triggers in one pass.

Drop All Triggers

EXEC sp_MSforeachtable @command1 = "DISABLE TRIGGER ALL ON '?'";

Drop All Foreign Keys (Required Before Dropping Tables)

EXEC sp_MSforeachtable @command1 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL";

Drop All Views

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += 'DROP VIEW ' + QUOTENAME(name) + ';'
FROM sys.views;
EXEC sp_executesql @sql;

Drop All Stored Procedures

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += 'DROP PROCEDURE ' + QUOTENAME(name) + ';'
FROM sys.procedures;
EXEC sp_executesql @sql;

Drop All Tables

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?";

Recommended Order

1. Disable/drop triggers
2. Drop or disable foreign key constraints
3. Drop views
4. Drop stored procedures
5. Drop tables last

Following this order avoids dependency errors, since SQL Server won't let you drop a table that's still referenced by a constraint, view, or procedure.