Drop any general 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 - 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.