This built in system stored procedure helps to Remove DataBase In Sql Server Without Using Wizard
- exec sp_dbremove 'dataBaseName'
the following is the entire script of the stored procedure
- USE [TMS]
- GO
-
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER procedure [sys].[sp_dbremove] --- 1996/04/08 00:00
- @dbname sysname = null,
- @dropdev varchar(10) = null
- as
- declare @dbid int
- declare @devname sysname
- declare @physname varchar(255)
-
- if @dbname is null
- begin
- raiserror(15131,-1,-1)
- return(1)
- end
-
- if lower(@dropdev) <> 'dropdev' and @dropdev is not null
- begin
- raiserror(15131,-1,-1)
- return(1)
- end
-
- -- You must be SA to execute this sproc.
- if (not (is_srvrolemember('sysadmin') = 1))
- begin
- raiserror(15247,-1,-1)
- return(1)
- end
-
- -- Make sure not trying to remove within a transaction.
- set implicit_transactions off
- if @@trancount > 0
- begin
- raiserror(15002,-1,-1,'sys.sp_dbremove')
- return(1)
- end
-
- -- Check to see if database exists.
- select @dbid = dbid from master.dbo.sysdatabases where name=@dbname
- if @dbid is null
- begin
- raiserror(15010,-1,-1,@dbname)
- return(1)
- end
-
- -- Make sure no one is in the db.
- if (select count(*) from master.dbo.sysprocesses where dbid = @dbid) > 0
- begin
- raiserror(15069,-1,-1)
- return (1)
- end
-
- -- Note: database @dbname may not exist anymore
- -- If invoke gets error, exception will abort this proc.
- EXEC %%DatabaseRef(Name = @dbname).SetSuspect(Value = 1)
-
- declare @dropCmd nvarchar(max)
-
- select @dropCmd = 'drop database ' + quotename(@dbname)
- exec (@dropCmd)
- raiserror(15458,-1,-1)
-
- return(0) -- sp_dbremove