Iterate through all non-system databases and empty them

On SQL Server 2012...I have multiple databases on a server used for training purposes. Periodically, these need to be cleared for a fresh start. When quantities of databases were small I would use Adam Anderson's code to remove all objects and manually change the USE statement. With 200+ databases now, I'd rather not. Looking to automate this process. I'm trying to use sp_MSforeachdb, without luck so far. Any suggestions? Thanks in advance!

```
DECLARE @command nvarchar(max)

SELECT @command ='IF EXISTS
(
SELECT 1
FROM sys.databases
WHERE name = ''?''
AND name LIKE ''learndb%'' --we have learndb1, learndb2, etc...
)
BEGIN

DECLARE @stmt nvarchar(max)
DECLARE @n char(1)
SET @n = char(10)

select @stmt = isnull( @stmt + @n, '''' ) +
''drop procedure ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.procedures

select @stmt = isnull( @stmt + @n, '''' ) +
''alter table ['' + schema_name(schema_id) + ''].['' + object_name( parent_object_id ) + ''] drop constraint ['' + name + '']''
from sys.check_constraints

select @stmt = isnull( @stmt + @n, '''' ) +
''drop function ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.objects
where type in ( ''FN'', ''IF'', ''TF'' )

select @stmt = isnull( @stmt + @n, '''' ) +
''drop view ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.views

select @stmt = isnull( @stmt + @n, '''' ) +
''alter table ['' + schema_name(schema_id) + ''].['' + object_name( parent_object_id ) + ''] drop constraint ['' + name + '']''
from sys.foreign_keys

select @stmt = isnull( @stmt + @n, '''' ) +
''drop table ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.tables

select @stmt = isnull( @stmt + @n, '''' ) +
''drop type ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.types
where is_user_defined = 1

select @stmt = isnull( @stmt + @n, '''') +
''drop trigger ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.objects

Try using sp_MSforeachdb - Aaron's version.

That is more reliable than the undocumented sp_MSforeachdb.

Also, it would be more plausible if you can just recreate the databases from scratch (like Aaron Suggested above), make sure you have Instant File initialization - Enabled.

StackExchange Database Administrators Q#75990, answer score: 5