Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

Sweeping your database clean

While sp_MSforeachtable does an impressive job discarding numerous objects, it's like using a sledgehammer when what we need is a scalpel. In other words, it lacks the grace to deal with nested dependencies and user-defined types. For a more surgical approach, we need to consider the interconnections between objects and perform cleanups in a calculated sequence.

Prioritizing dependencies

Dependencies, as many know, can be a real PITA, causing script crashes if not handled properly. To ensure a smooth erasure, follow these steps:

1. Identify all pesky foreign key constraints and drop them to eliminate table dependencies.
2. Proceed with dropping other dependent stuff like views and those unpredictable triggers.
3. At last, remove the tables themselves, now free from their chains of constraints and dependencies.

Dynamic SQL and system catalog views are excellent tools for drafting the necessary DROP statements.

Who's scripting who?

-- Admitting my variables addiction
DECLARE @DropScript NVARCHAR(MAX)
SET @DropScript = ''

-- Hunting down and eliminating those irksome constraints
SELECT @DropScript = @DropScript + 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + 
                    ' DROP CONSTRAINT ' + CONSTRAINT_NAME + ' ;'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE != 'CHECK' 

-- Pushing the red button... with no regrets!
EXEC sp_executesql @DropScript

-- Similar approach for triggers, views, SPs, and finally tables

-- Putting all one-liners on stage
EXEC sp_executesql @DropScript


Safety first, no speed limits though!

Always exclude system objects by running checks against the sys schemas. And employ transaction control to step back from changes if something goes sideways:

BEGIN TRANSACTION
-- Your cleansing script
COMMIT TRANSACTION -- or ROLLBACK TRANSACTION in case we need a plan B


Paving your path to rejuvenated databases

Database administrators often reset databases for testing or maintenance, if so, it's a good idea to encapsulate this logic within a stored procedure in the master database. Resetting any database can then be done with a single SP.

Every dawn a database reborn

A system-level stored procedure can look like this:

USE master 
GO 

CREATE PROCEDURE ResetDatabase @DatabaseName NVARCHAR(128) 
AS 
BEGIN
  -- Logic to purge all objects from the specified database
END 
GO


When invoked, this procedure resets your database, discarding all objects leaving a blank canvas.

But can it write poetry?

SQL Server's 'Generate Scripts' feature could add a more visual approach. You select the objects for removal, ensuring dependencies are borne in mind.

For more thorough entity removal, Adam Anderson's dynamic script might be the tool for you, handling cross-schema dependencies, and using DB_NAME() to target accurately and sorting objects in a dependency cycle.