Wednesday, March 25, 2009

Delete All Databases Except System Databases

Many times I require to create databases with different configurations for testing purpose.(This includes databases having special characters in their names, databases with different sizes, databases having data files on different locations. etc). As soon as I finish unit testing of the functionality , I have to delete those many databases manually from SQL Server Management Studio.

Today,Two times I had to delete 15 such databases manually :(. But now no more manual work. I have written SQL script for it :)

USE [master]
GO

DECLARE @database_name NVARCHAR (4000);

-- Collect all user created databases into cursor
DECLARE databases_cursor CURSOR FOR
SELECT name FROM sys.databases as d where d.database_id > 4
OPEN databases_cursor

FETCH NEXT FROM databases_cursor
INTO @database_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Dropping Database ' + @database_name
    DECLARE @cmd NVARCHAR(4000);
    SET @database_name = REPLACE(@database_name,']',']]');
    SET @cmd = 'ALTER DATABASE ['+@database_name+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ['+@database_name+'];'
    EXECUTE sp_executesql @cmd
    FETCH NEXT FROM databases_cursor
    INTO @database_name
END
CLOSE databases_cursor
DEALLOCATE databases_cursor

No comments: