Drop a Column and its Constraints and Indexes

,

Drops the specified column from the specified table as well as any constraints and indexes that depend on the column. By default the script will just find the corresponding column, constraints, and indexes.Comes in handy for patching databases.

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

CREATE PROCEDURE spDropColumnAndItsConstraintsAndIndexes
	@table SYSNAME = 'DefaultTable',
	@column SYSNAME = 'DefaultColumn',
	@DropConstraints INT = 0,
	@DropColumn INT = 0,
	@DropIndexes INT = 0
AS 
BEGIN
	SET NOCOUNT ON

	DECLARE @rollback       INT
	DECLARE @tableID        INT
	DECLARE @constraint     SYSNAME
	DECLARE @constrainttype NCHAR(1)
	DECLARE @index          SYSNAME
	DECLARE @sql            NVARCHAR(4000)
	
	SET @tableID = OBJECT_ID(@table)
	SET @rollback = 0

	DECLARE cur_constraints CURSOR FOR
		SELECT 	Cons.xtype, 
			Cons.[name]
			FROM dbo.sysobjects AS Cons WITH(NOLOCK)
			INNER JOIN (
				SELECT [id]
					 , colid
					FROM dbo.syscolumns WITH(NOLOCK)
					WHERE id = @tableID
					  AND name = @column) AS Cols
				ON  Cons.parent_obj = Cols.id
			WHERE ((OBJECTPROPERTY(Cons.[id],'IsConstraint') = 1
					AND	Cons.info = Cols.colid)
				OR (OBJECTPROPERTY(Cons.[id],'IsPrimaryKey') = 1))
			  AND Cons.parent_obj = @tableID

	DECLARE cur_indexes CURSOR FOR
		SELECT SI.name AS IndexName
			FROM dbo.sysindexes SI WITH (NOLOCK)
			INNER JOIN dbo.syscolumns SC WITH (NOLOCK)
				ON  SI.id = SC.id
			INNER JOIN dbo.sysindexkeys SIK WITH (NOLOCK)
				ON  SIK.id = SC.id
				AND SIK.colid = SC.colid
			INNER JOIN dbo.sysobjects SO WITH (NOLOCK)
				ON  SI.id = SO.id
			WHERE SI.indid !=0
			  AND OBJECTPROPERTY(OBJECT_ID(SI.name),'IsConstraint') = 0
			  AND SC.id = @tableID
			  AND SC.name = @column

	IF NOT EXISTS (
		SELECT 1 
			FROM dbo.sysobjects SO WITH(NOLOCK)
			INNER
			JOIN dbo.syscolumns SC WITH(NOLOCK) 
				ON SO.[id] = SC.[id]
			WHERE SO.name = @table 
			  AND SC.name = @column)
	BEGIN
		PRINT 'The column "'+@column+'" or the table "'+@table+'" does not exist.'
		GOTO errorExit
	END

BEGIN TRANSACTION

	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
			IF @@ERROR != 0
			BEGIN
				SET @rollback = 1
				GOTO errorExit
			END
			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
	CLOSE cur_constraints

	OPEN cur_indexes
	FETCH NEXT FROM cur_indexes INTO @index
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		SELECT @sql = N'ALTER TABLE '+@table+N' DROP INDEX '+@index
		IF @DropConstraints = 1
		BEGIN
			EXEC sp_executesql @sql
			IF @@ERROR != 0
			BEGIN
				SET @rollback = 1
				GOTO errorExit
			END
			PRINT 'DROPPING THE INDEX:'+@index+' FROM TABLE:'+@table
		END
		ELSE
			PRINT 'FOUND THE INDEX:'+@index+' ON TABLE:'+@table
		FETCH NEXT FROM cur_indexes INTO @index
	END
	CLOSE cur_indexes

	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
			IF @@ERROR != 0
			BEGIN
				SET @rollback = 1
				GOTO errorExit
			END
	END
	ELSE
		PRINT 'FOUND COLUMN:'+@column+' IN TABLE:'+@table
	errorExit:
	DEALLOCATE cur_indexes
	DEALLOCATE cur_constraints
	IF @rollback = 0 AND @@TRANCOUNT > 0
		COMMIT TRANSACTION
	ELSE
		ROLLBACK TRANSACTION
END
GO

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)