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:
Post a Comment