sp to drop multiple similarly named db

,

Procedure:    usp_drop_multiple_databases

Description:    This procedure will take a string as input (it can take the % character in the calling string), put all db like that name into a list, and then drop all the databases in the resultant list.
                   
Example calling syntax:
usp_drop_multiple_databases 'test_%_delete'
--> Will drop ALL databases with a name like
'test_%_delete'

Note:  Should be created in the master db and run with a master db connection.

/****************************************************************************************************************
Procedure:	usp_drop_multiple_databases 

Description:	This procedure will take a string as input (it can take the % character in the calling string), put all db like that name into a list, and then drop all the databases in the resultant list.
					
Example calling syntax: usp_drop_multiple_databases 
'test_%_delete'
-- Will drop ALL databases with a name like 'test_%_delete'
		
Note:         Should be created in the master db and run with a master db connection.
 
					
*****************************************************************************************************************/

                                                      
use master
go


if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[usp_drop_multiple_databases]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_drop_multiple_databases]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE usp_drop_multiple_databases @table_like varchar(50)
AS
declare @drop_list nvarchar (3800)

SELECT @drop_list = isnull(@drop_list + ', ', '') + name
from sysdatabases where name like @table_like
and name not in ('master','model','msdb','tempdb') 
ORDER BY name

select @drop_list as 'drop list'
declare @drop_statement nvarchar(4000)
select @drop_statement = 'drop database ' + @drop_list

execute sp_executesql @drop_statement

go

SET QUOTED_IDENTIFIER OFF 
GO

Rate

Share

Share

Rate