Friday, 27 August 2021

Dynamically Drop all databases in sql server

 -- All Drop all databases from sql server
ALTER PROCEDURE SP_Drop_AllDB
AS
BEGIN
DECLARE @command NVARCHAR(MAX);
SET @command = N'';
SELECT @command
= @command 
  + N'ALTER DATABASE [' + [name] + N']  SET single_user with rollback immediate;' + CHAR(13) + CHAR(10)
  + N'DROP DATABASE [' + [name] + N'];' + CHAR(13) + CHAR(10)
FROM [master].[sys].[databases]
WHERE [name] NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'ReportServer' );
SELECT @command;
--EXECUTE sp_executesql @command
END 

No comments:

Post a Comment