DROPping all Databases on an Instance

Leave a comment

June 2, 2011 by Mike Hillwig

I’m doing a testing my database restores. Notice that I say I’m testing the restores. If the backups don’t work, neither will the restores.

Part of my process is to drop the existing databases in the restore target instance. It’s really the only place I can ever imagine this script seeing the light of day.

SET NOCOUNT ON
DECLARE @v_dbname nvarchar(100)
DECLARE @v_SQL nvarchar(1000)
DECLARE c_databases CURSOR FOR
SELECT [name]
FROM sysdatabases
WHERE name NOT IN ('tempdb', 'pubs', 'master', 'msdb', 'litespeedlocal', 'model')
ORDER BY [name]
OPEN c_databases
FETCH NEXT FROM c_databases INTO @v_dbname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
select @v_SQL = 'DROP DATABASE [' + @v_dbname + ']'
exec ( @v_sql )
END
FETCH NEXT FROM c_databases INTO @v_dbname
END
CLOSE c_databases
DEALLOCATE c_databases