Easily delete database objects

use master
go

Create procedure dbo.sp_DropDatabaseObject 
 @pSchemaName varchar(100) -- the schema the object belongs to, when applicable
 ,@pObjectName sysname -- name of the object to drop, including schema (i.e. dbo.TableName)
 ,@pObjectType char(2) -- type of object to be dropped. 
 -- Can be 'U', 'V', 'P', 'FN', 'I' (for table, view, procedure, function, and index)

as

----------------------------------------------------------------------------
-- Declarations
----------------------------------------------------------------------------
declare -- Standard declares
 @FALSE tinyint -- Boolean false.
 ,@TRUE tinyint -- Boolean true.
 ,@ExitCode int -- Return value of this procedure.
 ,@rc int -- Return code from a called SP.
 ,@Error int -- Store error codes returned by statements and procedures (@@error).
 ,@RaiseMessage varchar(1000) -- Creates helpful message to be raised when running.

declare -- sp specific declares
 @SingleQuote nchar(1)
 ,@SQL nvarchar(4000)
 ,@IndexTableName varchar(50)
 ,@IndexIndexName varchar(50)

----------------------------------------------------------------------------
-- Initializations
----------------------------------------------------------------------------
select -- Standard constants
 @FALSE = 0
 ,@TRUE = 1
 ,@ExitCode = 0
 ,@rc = 0
 ,@Error = 0

Select
 @SingleQuote = char(39)

----------------------------------------------------------------------------
-- Validate that all objects have an appropriate ObjectType
----------------------------------------------------------------------------
if @pObjectType not in ('U', 'V', 'P', 'FN', 'I') begin
 select @RaiseMessage = 'Invalid ObjectType value: ' + @pObjectType
 goto ErrorHandler
end

----------------------------------------------------------------------------
-- Put together the SQL to drop the database object
----------------------------------------------------------------------------
if @pObjectType = 'U' begin
 if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
 -- The table exists, prepare to delete it
 Select @SQL = 'Drop table ' + @pSchemaName + '.' + @pObjectName 
 end 
 else begin
 select @RaiseMessage = 'Table ' + @pObjectName + ' does not exist or has already been deleted'
 print @RaiseMessage
 goto ExitProc
 end
end

if @pObjectType = 'V' begin
 if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
 -- The view exists, prepare to delete it
 Select @SQL = 'Drop view ' + @pSchemaName + '.' + @pObjectName 
 end 
 else begin
 select @RaiseMessage = 'View ' + @pObjectName + ' does not exist or has already been deleted'
 print @RaiseMessage
 goto ExitProc
 end
end

if @pObjectType = 'P' begin
 if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
 -- The procedure exists, prepare to delete it
 Select @SQL = 'Drop procedure ' + @pSchemaName + '.' + @pObjectName 
 end 
 else begin
 select @RaiseMessage = 'Procedure ' + @pObjectName + ' does not exist or has already been deleted'
 print @RaiseMessage
 goto ExitProc
 end
end

if @pObjectType = 'FN' begin
 if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
 -- The function exists, prepare to delete it
 Select @SQL = 'Drop function ' + @pSchemaName + '.' + @pObjectName 
 end 
 else begin
 select @RaiseMessage = 'Function ' + @pObjectName + ' does not exist or has already been deleted'
 print @RaiseMessage
 goto ExitProc
 end
end

if @pObjectType = 'I' begin
 -- Parse out the table/index names to be able to test for index existance easily
 Select @IndexTableName = substring(@pObjectName, 1, CHARINDEX('.', @pObjectName) - 1) 
 Select @IndexIndexName = substring(@pObjectName, CHARINDEX('.', @pObjectName) + 1, 50 )
 If IndexProperty(OBJECT_ID(@IndexTableName),@IndexIndexName,'IndexID') IS not NULL begin
 -- Check first whether it's a primary key
 if exists 
 (
 select * from sys.indexes where is_primary_key = @TRUE and object_name(object_id) = @IndexTableName and name = @IndexIndexName
 ) 
 begin
 Select @SQL = 'Alter table ' + @pSchemaName + '.' + @IndexTableName + ' drop constraint ' + @IndexIndexName
 end
 else begin
 Select @SQL = 'Drop Index ' + @pSchemaName + '.' + @pObjectName 
 end
 end
 else begin
 select @RaiseMessage = 'Index ' + @pObjectName + ' does not exist or has already been deleted'
 print @RaiseMessage
 goto ExitProc
 end 
end

----------------------------------------------------------------------------
-- Drop the database object
----------------------------------------------------------------------------
if @SQL is not null begin
 Exec @RC = sp_executesql @sql
 select @Error = @@Error
 if @Error <> 0 or @RC <> 0 begin
 select @RaiseMessage = 'Error dropping object : ' + @pObjectName + ' using sql statement: ' + @SQL
 goto ErrorHandler 
 end
 Select @RaiseMessage = 'Completed dropping object: ' + @pObjectName + ' using sql statement: ' + @SQL
 print @RaiseMessage
end

goto ExitProc

----------------------------------------------------------------------------
-- Error Handler
----------------------------------------------------------------------------
ErrorHandler:

 select @ExitCode = -100

 -- Print the Error Message now that will kill isql.
 RAISERROR (
 @RaiseMessage
 ,16 -- Severity.
 ,1 -- State.
 )

 goto ExitProc

----------------------------------------------------------------------------
-- Exit Procedure
----------------------------------------------------------------------------
ExitProc:

 return (@ExitCode)

go


-- Marks it as a system object. Otherwise, it may return object information from the master database instead of the calling database
EXEC sys.sp_MS_marksystemobject sp_DropDatabaseObject
GO