Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Drop Object procedure

Author  Topic 

DrewBurlingame
Starting Member

49 Posts

Posted - 2005-12-09 : 20:19:41
I've recently started writing my own scripts to create a database instead of using EM.
I quickly tired of copying and pasting the typical if exists then drop statements, so I created this procedure to save me some time.
I know it doesn't take into account all of the possible data types, and I know
that I'm not suppossed to query sysobjects directly, but it gets the job done for me.
If you'd like to refactor it, please do.


/**********************************************************************
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
   

- Advertisement -