Drop a Column and All Corresponding Constraints

,

Drops the specified column from the specified table as well as any constraints that depend on the column. By default the script will just find the corresponding column and constraints.

Comes in handy for patching databases.

-- Author: Andy Brown (andy.brown@mm-games.com)

IF EXISTS (SELECT 	1 
		FROM 	sysobjects 
		WHERE 	[name] = 'spDropColumnAndConstraints'
		AND 	xtype = 'P')
	DROP PROCEDURE spDropColumnAndConstraints
GO

CREATE PROCEDURE spDropColumnAndConstraints
	@table sysname = 'DefaultTable',
	@column sysname = 'DefaultColumn',
	@DropConstraints int = 0,
	@DropColumn int = 0
AS 
BEGIN
	SET NOCOUNT ON

	DECLARE @tableID int
	DECLARE @constraint sysname
	DECLARE @constrainttype nchar(1)
	DECLARE @sql nvarchar(4000)
	
	SET @tableID = object_id(@table)
	
	DECLARE cur_constraints CURSOR FOR
		SELECT 	Cons.xtype, 
			Cons.[name]
			FROM	[sysobjects] as Cons WITH(NOLOCK)
			INNER JOIN	
				(select	[id],
					colid 
					FROM	syscolumns WITH(NOLOCK)
					WHERE	id = @tableID
					AND 	name = @column) AS Cols
				ON	Cons.parent_obj = Cols.id
				AND 	Cons.info = Cols.colid
					
			WHERE	Cons.xtype in ('C', 'F', 'PK', 'UQ', 'D')
			AND 	@tableID = Cons.parent_obj
	
	IF EXISTS (SELECT	1 
			FROM	sysobjects WITH(NOLOCK)
			INNER JOIN
				syscolumns WITH(NOLOCK) 
				ON	sysobjects.[id] = syscolumns.[id]
			WHERE	syscolumns.name = @column 
			AND	sysobjects.name = @table)
	BEGIN
		OPEN cur_constraints

		FETCH NEXT FROM cur_constraints INTO @constrainttype, @constraint
		WHILE (@@FETCH_STATUS = 0)
		BEGIN
			SELECT @sql = N'ALTER TABLE '+@table+N' DROP CONSTRAINT '+@constraint
			IF @DropConstraints = 1
			BEGIN
				EXEC sp_executesql @sql
				PRINT 'DROPPING THE CONSTRAINT:'+@constraint+' (id:'+CAST(object_id(@constraint) AS VARCHAR)+') OF TYPE:'+@constrainttype+' FROM TABLE:'+@table
			END
			ELSE
				PRINT 'FOUND THE CONSTRAINT:'+@constraint+' (id:'+CAST(object_id(@constraint) AS VARCHAR)+') OF TYPE:'+@constrainttype+' ON TABLE:'+@table
			FETCH NEXT FROM cur_constraints INTO @constrainttype, @constraint
		END

		SET @sql = N'ALTER TABLE dbo.'+@table+N' DROP COLUMN '+@column
		IF @DropColumn = 1
		BEGIN
			PRINT 'DROPPING COLUMN:'+@column+' FROM TABLE:'+@table
			EXEC sp_executesql @sql
		END
		ELSE
			PRINT 'FOUND COLUMN:'+@column+' IN TABLE:'+@table
		CLOSE cur_constraints
		DEALLOCATE cur_constraints
	END
	ELSE
		PRINT 'The column "'+@column+'" or the table "'+@table+'" does not exist.'
END
GO

Rate

5 (1)

Share

Share

Rate

5 (1)