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 PROCEDURE dbo.TestProc
AS BEGIN SELECT 1 END
GO
CREATE FUNCTION dbo.TestFunc(@i int)
RETURNS INT
AS BEGIN RETURN @i END
GO
SELECT * FROM sys.objects WHERE name IN ('TestProc', 'TestFunc')
EXECUTE dbo.DropObject 'TestProc'
EXECUTE dbo.DropObject 'TestFunc'
EXECUTE dbo.DropObject 'TestNotExists'
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.
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.