Click here to Skip to main content
Click here to Skip to main content

Drop any general SQL Server object

, 29 Jan 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Simple SP to drop any general type of SQL Server object

Introduction

This stored procedure / C# method can drop any type of SQL Server object. It detects the object type from sys.objects and then calls the appropriate DROP command.

A few situations where this is useful

  • As part of database update code where old versions might have objects of different types sharing names (SPs that later changed to TVFs in future versions, etc).
  • Wrap in a cursor for easy dropping of batches of contents (all the methods of a CLR assembly, for example).
  • Already includes built-in IF EXISTS checking - you can safely call this on objects that do not exist.

Pro's/con's

  • No support for multiple schemas - can easily be added if needed.
  • Just like DROP TABLE relationships will still need to be removed first under certain circumstances.
  • Can't drop *anything* - triggers, keys, constraints, defaults aren't coded in - add at your leisure.
  • As with any tool - don't go shooting with it at everything you see. You might just end up dropping some things that somebody actually cared about.

The code

CREATE PROCEDURE dbo.DropObject(@objectname varchar(max))
AS
BEGIN
  DECLARE @ObjectType varchar(max) = (SELECT [type] FROM sys.objects WHERE name = @objectname)
  DECLARE @DropType varchar(max) = ''
  
  IF @ObjectType IN ('PC', 'P')
    SELECT @DropType = 'PROCEDURE'

  IF @ObjectType IN ('FN', 'FS', 'FT', 'IF', 'TF')
    SELECT @DropType = 'FUNCTION'
    
  IF @ObjectType = 'AF'
    SELECT @DropType = 'AGGREGATE'
    
  IF @ObjectType = 'U'
    SELECT @DropType = 'TABLE'
    
  IF @ObjectType = 'V'
    SELECT @DropType = 'VIEW'
 
  IF @DropType <> ''
    EXEC('DROP '+ @DropType + ' [' + @objectname + ']')
END

Using the code

Simply call the SP with the name of the object to drop it. An example follows:

--Create a test SP to drop
CREATE PROCEDURE dbo.TestProc
AS BEGIN SELECT 1 END
GO
--Create a test function to drop
CREATE FUNCTION dbo.TestFunc(@i int)
RETURNS INT
AS BEGIN RETURN @i END
GO
--Show that the objects are there
SELECT * FROM sys.objects WHERE name IN ('TestProc', 'TestFunc')
--Drop the test SP
EXECUTE dbo.DropObject 'TestProc'
--Drop the test function
EXECUTE dbo.DropObject 'TestFunc'
--Drop something that does not exist
EXECUTE dbo.DropObject 'TestNotExists'
--Show that the objects have been dropped
SELECT * FROM sys.objects WHERE name IN ('TestProc', 'TestFunc')

For easy reuse

If you work with hundreds of databases and can't be bothered to deploy it everywhere, here's a C# version that you can use from your code / deploy using CLR. Just replace the database commands with your respective wrappers and you're good to go.

/// <summary>
/// Drops the object with ID object_id from the database.  
/// </summary>
/// <param name="object_id">The object to drop.</param>
public void DropObject(int? object_id)
{
    var row = DB.GetRow("SELECT name, type FROM sys.objects WHERE object_id = @object_id", object_id);
    
    var type = (row["Type"] as string ?? "").ToUpper();
    var name = row["Name"] as string;

    if (type.IsAnyOf("PC", "P"))
        DB.Execute("DROP PROCEDURE [" + name + "]");

    if (type.IsAnyOf("FN", "FS", "FT", "IF", "TF"))
        DB.Execute("DROP FUNCTION [" + name + "]");

    if (type.IsAnyOf("AF"))
        DB.Execute("DROP AGGREGATE [" + name + "]");

    if (type.IsAnyOf("U"))
        DB.Execute("DROP TABLE [" + name + "]");

    if (type.IsAnyOf("V"))
        DB.Execute("DROP VIEW [" + name + "]");
}

For the C# version, you'll probably need to paste IsAnyOf somewhere in your codebase or write out a bunch of || statements (I prefer to let the compiler do the work Wink | ;) - CPUs are cheap nowadays):

public static bool IsAnyOf<T>(this T cur, params T[] values)
{
    if (values != null)
        foreach (var val in values)
            if ((cur == null && val == null) || (cur != null && cur.Equals(val)))
                return true;

    return false;
}

History

  • 2013/01/01 - Tip created.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Michiel du Toit
Software Developer Coderon Technologies
South Africa South Africa
Michiel du Toit is a software developer based in Bloemfontein, South Africa focusing on development using C# and SQL Server (both WinForms and ASP.NET).

Comments and Discussions

 
GeneralMy vote of 5 PinmemberS. M. Ahasan Habib7-Feb-13 17:06 
QuestionI cant see the benefit. Pinmembersucram2-Jan-13 2:30 
AnswerRe: I cant see the benefit. PinmemberMichiel du Toit2-Jan-13 2:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141220.1 | Last Updated 29 Jan 2013
Article Copyright 2013 by Michiel du Toit
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid