June 21, 2016 22:08 by
PeterToday, I will explain you about how to Remove DataBase In Sql Server Without Using Wizard. This system stored procedure will helps you.
exec sp_dbremove 'dataBaseName'
The following is the entire script of the stored procedure:
USE [TMS]
GO
/****** Object: StoredProcedure [sys].[sp_dbremove] Script Date: 08-12-2014 22:09:09 ******/
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
HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.
