/**********************************************************************
procDropConstraint
 - This procedure simplifies dropping a constraint from the database.

 sysDropObject 'FK_NodeAudit_Node'
 
Examples:
 
  EXEC sysDropObject 'FK_Node_Document'
  EXEC sysDropObject 'Document'
  EXEC sysDropObject 'Node'
**********************************************************************/
-- 
CREATE PROCEDURE sysDropObject
	@Name varchar(100)
AS
DECLARE
	@xtype nvarchar(2),
	@parentxtype nvarchar(2),
	@parentname nvarchar(100),
	@sql nvarchar(200)

SELECT
	@xtype = child.xtype, @parentxtype = parent.xtype, @parentname = parent.name
FROM
	dbo.sysobjects child
	left outer join dbo.sysobjects parent on child.parent_obj = parent.id
WHERE
	child.name = @Name
	AND child.xtype NOT IN ('S','D') --system tables and extended properties
	AND NOT (child.xtype = 'P' and LEFT(child.name, 3) = 'dt_') --local system proc
	AND NOT (child.xtype = 'V' and LEFT(child.name, 3) = 'sys') --local system views

IF @xtype is null GOTO _NothingToDoHere

IF @xtype IN ('F','PK','UQ')
  BEGIN
	SET @sql = N'ALTER TABLE [' + @parentname + '] DROP CONSTRAINT [' + @Name + ']'
	GOTO _ExecSql
  END

IF @xtype = 'P'
  BEGIN
	SET @sql = N'DROP PROCEDURE [' + @Name + ']'
	GOTO _ExecSql
  END

IF @xtype = 'U'
  BEGIN
	SET @sql = N'DROP TABLE [' + @Name + ']'
	GOTO _ExecSql
  END

_Fail:
	RAISERROR( 'Unhandled xtype ''%s'' for %s', 16, 1, @xtype, @Name )
	RETURN 1
_ExecSql:
 	EXEC sp_executesql @sql
	PRINT 'sysDropObject: executed - ' + @sql 
	RETURN 0
_NothingToDoHere:
	PRINT 'sysDropObject: attempted to drop ' + @Name + ', but it does not exist to be dropped.'
	RETURN 0

GO