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)
Thank this author by sharing:
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 intif 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 tableexec sp_DropDatabaseObject 'dbo', 'TestTable', 'u'-- Drop procedureexec sp_DropDatabaseObject 'dbo', 'TestProcedure', 'p'-- Drop indexexec sp_DropDatabaseObject 'dbo', 'TestTable.index1', 'i'-- Drop Viewexec sp_DropDatabaseObject 'dbo', 'TestView', 'v'-- Drop functionexec sp_DropDatabaseObject 'dbo', 'TestFunction', 'fn'
Stored procedure error handler
Database Integrity Error
Error in COALESCE stored Procedure
How to have a Stored Procedure work on another database