Click here to Skip to main content
12,999,744 members (40,945 online)
Click here to Skip to main content
Add your own
alternative version


4 bookmarked
Posted 1 Jan 2013

Drop any general SQL Server object

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


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.


  • 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))
  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 + ']')

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 a test function to drop
CREATE FUNCTION dbo.TestFunc(@i int)
--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;


  • 2013/01/01 - Tip created.


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


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).

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
S. M. Ahasan Habib7-Feb-13 16:06
memberS. M. Ahasan Habib7-Feb-13 16:06 
QuestionI cant see the benefit. Pin
sucram2-Jan-13 1:30
membersucram2-Jan-13 1:30 
AnswerRe: I cant see the benefit. Pin
Michiel du Toit2-Jan-13 1:46
memberMichiel du Toit2-Jan-13 1:46 

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

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

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