SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Easily delete database objects

By Sylvia Moestl Vasilik,

Any developer working on larger, more complex systems, will eventually need to do some massive cleanup of tables, stored procedures, and other objects that are no longer used. Although you could just write a simple statement like this:

Drop table TestTable

...you should really have something more robust, that returns informative messagess and checks for errors. Something like this:

Declare @Error int
if exists (select * from sys.objects where name = 'TestTable' and type = 'u' and schema_id = schema_id('dbo') ) begin
-- The table exists, prepare to delete it
Drop table dbo.TestTable
Select @Error = @@Error
if @Error <> 0 begin
RAISERROR ('Error dropping table dbo.TestTable' ,16 ,1)
print 'Successfully dropped Table TestTable.'
else begin
print 'Table TestTable does not exist or has already been deleted.'

But do you want to constantly be rewriting that piece of code as you need to drop different objects? Absolutely not! That's why I wrote the stored procedure above called sp_DropDatabaseObject that will delete many different types of database objects (tables, procedures, views, functions, and indexes). It incorporates all the functionality above (error trapping, good error messages), in a reusable procedure. It's created in the master database, so that it can be called from any database. Note that at the end, I call sp_MS_marksystemobject to mark it as a system object - this allows it to have the context of the calling database even though it's located in the master database.

Here are some examples of how to run sp_DropDatabaseObject

-- Drop table
exec sp_DropDatabaseObject 'dbo', 'TestTable', 'u'
-- Drop procedure
exec sp_DropDatabaseObject 'dbo', 'TestProcedure', 'p'
-- Drop index
exec sp_DropDatabaseObject 'dbo', 'TestTable.index1', 'i'
-- Drop View
exec sp_DropDatabaseObject 'dbo', 'TestView', 'v'
-- Drop function
exec sp_DropDatabaseObject 'dbo', 'TestFunction', 'fn'





Total article views: 1436 | Views in the last 30 days: 2
Related Articles

Stored procedure error handler

Stored procedure error handler


Database Integrity Error

Database Integrity Error


Connecting to Database ERROR

Database Error


Error in Stored Procedure

Error in COALESCE stored Procedure


Stored Procedure & 'other' database

How to have a Stored Procedure work on another database