Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved 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)
end
print 'Successfully dropped Table TestTable.'
end
else begin
print 'Table TestTable does not exist or has already been deleted.'
end

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: 1407 | Views in the last 30 days: 1
 
Related Articles
FORUM

Stored procedure error handler

Stored procedure error handler

FORUM

Database Integrity Error

Database Integrity Error

FORUM

Connecting to Database ERROR

Database Error

FORUM

Error in Stored Procedure

Error in COALESCE stored Procedure

FORUM

Stored Procedure & 'other' database

How to have a Stored Procedure work on another database

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones